Conditional row color based on a cell value

Jeremy (US) asked me a question about coloring based on a cells value:

First your product ASAP Utilities is great and has been a life saver. One feature that would be outstanding is a conditional row color based on a cell value.
For instance there are numerous times I have to color a row because the value of a cell in a column is set either TRUE of FALSE or 0 or 1 and I currently do this by hand review.
If the ASAP utilities has a way to do this already I would love to know how.

Excel has a great built-in function to achieve this: Conditional Formatting (In the menu Format » Conditional formatting). The first time it might be a little difficult to find your way to do this but if you spend a little time with it you will soon realize it is powerful function.

In the following example if we want the rows to be colored blue when column C is true and orange if the value is 1, we can use the following formula's:
Select all the cells that need to be colored. Usually we color a cell based an its value and use the [Cell Value Is ] in the conditional formatting box. To format cells based on other cells you need the [Cell Formula Is].
We use the =INDIRECT() function to get the value of column C for each row:
If the value is TRUE : =INDIRECT("C"&ROW())=TRUE
If the value is 1 : =INDIRECT("C"&ROW())=1

The result:

Download the example workbook

Interesting links:
http://www.cpearson.com/excel/cformatting.htm
http://www.contextures.com/xlCondFormat01.html

A "quick and dirty" method is using:
ASAP Utilities » Select » Conditional row and column select, hide or delete
and then color the selected rows.

109 comments

Anthony

I just wanted to start by saying thank you for this very informative post! This has saved me from very redundant work.

I do have a question and hopefully someone can answer. I am able to get this to work but what if I'm needing to have a row highlighted containing "Missing Information: Etc Etc Etc" and the row highlights because it includes the text "Missing Information" ON TOP of everything else included into the cell. I find that the current format =INDIRECT(”C”&ROW())="Missing Information" will only highlight when the cell equals "Missing Information" rather than containing it and additional text.

I hope this is clear enough to understand.

Thank you!!

Selcuk

Thanks a lot. Great tip.

Ritayan B

Thanks a lot, works wonderfully :-)

Jennifer

This is amazing and you are my hero. This is going into my queue with vlookups and sumifs.

Paul

Works great for me.

Now I just need to know how to do exactly the same but for columns instead of rows.

What would the formula be for that?

Paul

Thomas

I had a pretty complex excel sheet and tried to apply this with many other attempts of conditional formatting and found after about 20 minutes I found out I had to use something similar to:

=INDIRECT("$C"&ROW())=TRUE

Alan

I hvae been searching for the formula to get an individual cell to change color dependant upon the test in the cell IE if a cell in row G contains the word APPLE i want that cell and any others in the column with the same text to be red. I have not found anything that fits this description yet, I have used the following to make it work for a date,
flormula is =ISBLANK(E1:E100)=TRUE
CELL VALUE IS LESS THAN =NOW()
CELL VALUE IS LESS THAN =NOW()+30 (MEANING IN TEH NEXT 30 DAYS)

ANY HELP IS A PPRECIADTE.

Satish

I want to do conditional formatting based on cell color.Means, if the cell color is green, should get a comment Closed, if red, cancelled & if white, should get open.how to do it?

Nathan

I have tried the origanal formula but cant get it to work on my worksheet. I am using 2007. In column Q when i type VOID into Q4, Q5, Q6 for example i want the whole row to turn a different colour. Can someone provide me with the correct formula to try? Thanks!