Conditional Formating 3-Color Scale

Question

I am new to excel. I am trying to figure out how to do the 3 color scale.  I select all cells I will be working in then I click conditional formatting > New Rule > 3-Color Scale

I think choose the 3 colors I want > Then I chose Type = Formula and the Value =$A$4 ="Medium",=$A$4 ="Important",=$A$4 ="Critical",

What I am trying to do is highlight the entire row based on column A's Value starting at A4. But it doesn't seem to be working. If you need anymore information please let me know.

Answer 1

Hello,

the color scale conditional formats work with numeric values only. If you want to highlight rows when a specific cell shows a text value, then you need a different approach.

For example, column A has the possible text values Medium, Important and Critical. Select all the values in column A and all of the columns that you want to highlight.

Then click Conditional Formatting > New rule > Use a formula to determine ...

If your selection starts in row 1, then use a formula like this:

=$A1="Critical"

select a format. Then create another rule with the formula

=$A1="Medium"

and select a format. Define a third rule for

=$A1="Important"

and select a format.  Note that if your top left cell is not in row 1 you will need to adjust the formulas to reflect the corresponding row, otherwise the results will be off. The placement of the $ sign is crucial. It anchors the criterion cell in column A, but will refer to the current row, so in each row, the conditional format will look at column A to determine which color to apply.

Hope that makes sense.

cheers, teylyn

Answer 2

Thanks for the quick response and that worked.