Conditional Formatting a Row based on cell value with Excel

Question

I have looked at multiple sites that show how to do Conditional Formatting in Excel for a row based on a cell value. But all of them use equations as there formula. For example, if cell value is 5, highlight the row blue. I am trying to do it with text. If cell value is "Completed", highlight the row green.

This is the formula I was able to get from http://smallbusiness.chron.com/format-row-based-value-cell-excel-2007-40595.html

=INDIRECT("B"&ROW())>0

and modified it to

=INDIRECT("F"&ROW())="Completed"

Basically I would like the row (from column A to F) to be highlighted Green if cell value in F (whichever row)=Completed.

Grade 'Eh' Bacon · Accepted Answer · 2015-08-06 14:23:27Z

3

In my opinion the site you were using overcomplicated the issue by suggesting the "Indirect" function.

Instead, highlight your block of data (let's say A1:F10), and put in the following rule. Remember that conditional formatting is organized so that the formula is directed at the top-left cell in the selected area. So we will write this formula specifically for A1, keeping in mind that we want it to apply to all cells from A1:F10. To do that, we will need to keep "F" as the absolute column (because we only ever want to check F), but we will want to make row 1 a relative row (because we want to check the particular row that changes as the formatting moves down the data block.

This would look simply as follows:

=$F1="Completed"

However from the way your question is written, it looks like that should work, so something else is going on there. If my suggestion doesn't solve your issue then please provide an example of what your data actually looks like - for example, are you sure that the rows you applied the rule to include any cells where F(x) = "Completed"?

answered Aug 6, 2015 at 14:23

Grade 'Eh' Bacon

3,8534 gold badges26 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jack Armstrong Over a year ago

Okay I just did that and it only highlighted the cells in columnn E and F. I will provide a copy of what I am working on now.

Grade 'Eh' Bacon Over a year ago

@JackArmstrong If it ony highlights E & F, then my guess is that you are not applying the rule to all of the cells. Are you sure you set this rule to apply all the way from A:F?

Jack Armstrong Over a year ago

Okay now it is working. For some reason it only formatted those cells, but when I did a quick change of size it reformatted the rest of the row. Now to figure out why it doesn't update everything. And yes it was applying from A:F.

Collectives™ on Stack Overflow

Conditional Formatting a Row based on cell value with Excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related