ASAP Utilities General

Conditional row color based on a cell value

by Bastien 109 Comment

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.

Related Posts

109 comments

Kris

Had to wrap my value in quotes to achieve this: =INDIRECT("A"&ROW())="Old" . . . maybe because it was being interpreted as a string as opposed to a value?

SHINTO MATHEW

Thank You Very Much……..
I am looking for this trick for the last six months or so
thanks a lot

Willie

in Excel 2007 I need to conditally format a row (fill with color) based on text rather than a numeric value. I have five worksheets, and in all of those worksheets I need the rows highlighted based on BrandOne, BrandTwo, BrandThree… arrrgh… I can't seem to get the formula right to get the entire row highlighted. When I say "BrandOne" it only highlights that cell and not the entire row… Any suggestions?

Bastien

Hello Willie,

You have to use the conditional formatting for all cells in the entire row. Did you do that?

Kind regards,
Bastien Mensink

Rajratna

Thanks a lot.
That helped.

Zedd

This is wonderfull. I have been looking for something like this for a long time. Lot's of website that I used for reference didn't have any examples, but you did and it helped me see what I needed to do. Thanks for the great help.

Fred Michel

This is by far the Best code I've found. I knew it existed and I've been looking for it since yesterday. Works like a Charm…if you are using strings instead of numbers, just put them like that: "STRING" and it works.

I would only like to find out one thing…the Exel Conditional Formating only lets you create 3 contidions! I am working with 5 possible values to change my row color. How can I do that?

Bastien

Hello Fred,

Until Excel 2007 you only have three options for conditional formatting. In Excel 2007 this limit was removed and it is only limited by available memory.
There are workarounds by using macros to set the conditional formatting, but that is more complex and the performance is slower than the built-in method.

Kind regards,
Bastien

Fred Michel

Hi Bastien,

Thanks for your reply. Unfortunately we don’t have Excel 2007 here at work?. I do have it Home…but I don’t think it would work if I create 5 conditions on Excel 2007 and then export the file to Excel 2003.

I am not too familiar with Macros either, and they have been disabled here for security reasons…Hmmm, I don’t know what to do then.

So if beside the macro, there is no other way…I’m in trouble…lol!

Bastien

Hello Fred,

Creating it in 2007 with 5 conditions wouldn't bring the 5 conditions to 2003.
As an alternative you could use certain values instead of only colors by using the IF formula in a cell to display a certain value if a condition is met.

You can also use a custom number format to specify a font color, for example
[Red][< =10]#;[Green][>10]#;General
Although this is not very good documented and it seems to be limited to two conditions.
http://office.microsoft.com/en-us/excel/HP051995001033.aspx

The following page is a valuable resource on conditional formatting:
http://www.mvps.org/dmcritchie/excel/condfmt.htm

If the numbers do not change a lot you can use ASAP Utilities to select certain values and then color them:
ASAP Utilities » Select » Conditional select cells…
//www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=7&utilities=Select

Kind regards,
Bastien

Rachael

hi was wondering if someone could help me. i am using =INDIRECT("K"&ROW())="Converted" formula and work like a dream…

however i now need to add extra part. i am trying to change the colour of the row if column "N" states its Invoiced.

so column K is status. colun N is invoiced or not….

i want row to be Green if status in colum K is "Converted" – got that far using the above BUT now i was row to Red if Colum K is "Converted" AND colun N is "invoiced"

anyone help??? PLease

Bastien

Hello Rachel,

If you want two conditions in your formula you can use the =AND() formula.
Please have a look in Excel's help on this formula.

Kind regards,
Bastien

Maha

The below formula is working fine but =INDIRECT("C"&ROW())=false is not working. Can any one help on this??

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

Bastien

Hello Maha,

The formula should work. However the formula =INDIRECT("C"&ROW())=FALSE also returns TRUE if a cell is empty.

Kind regards,
Bastien

Alex

Thanks, that works great!!!

Deven

Thanks you so much. I went thru couple of web sites. your solution was so easy to follow.

Tom

WHat if I want to color a back ground based on alphanumeric values? I need to change the color to red if the value has an "R" in it such as 15R or 1R and AMBER if the value has an "A" in it such as 1A or 18A

please answer by email??

Bastien

Hello Tom,

You can do this by using the FIND() functions.
You can find an example of that on the following page:
http://chandoo.org/wp/2009/03/31/search-with-conditional-formatting/

Kind regards,
Bastien

Susan Crowe

Thank you! my brain was about to explode typing to solve that one!

you have saved my sanity :-)

BEW

How do you do the opposite, that is color the column based on a value in the first row. in your example I want the rows to be colored blue when Row 1 is 'Col D'

Bastien

Hello Bew,

In that case you can use the following formula: =$D$1=TRUE

Kind regards,
Bastien

BEW

thanks, maybe my question wasn't very clear..

in the example table at the top
I want a conditional formatting formula so that if in Row 2 (A2:F2 in this example) any value is "blah 71" then the whole column would be coloured (D2:D17 in this case)

obviously in the table above there is only one occurance of "blah 71" in Row 2, but what if that value was present several times in a bigger table?

Bastien

Hello BEW,

The condidtional formatting is set for each cell. So if you want the entire column to be colored you have to set the conditional formatting for all the cells in the column.
If the value appears more than once it works the same as when it appears once, the formula result is TRUE if the values appears (once or more) in the given range.

Kind regards,
Bastien

Jon Gallant's Blog

How To Highlight Rows Based on a Cell Value in Excel…

Here’s how to highlight rows based on a cell value in Excel 2007 and Excel 2010.  Reference this…

Karan

Hey man this is really helpfull, i have been searching for this for a long time. Thnaks a lot and God bless You!!!

Darsen

Quick question:

If I want to take a list of two numbers, and if the difference between those numbers is greater than 5000 color it red, and if it is less than 5000 but greater than 2000 it is yellow and below that is not colored. How would I achieve this? I do not want to add any summary data in another column, is it possibe to use the function to calculate this for all items in a specific column?

Darsen

Andie

Hi

Thanks for this, saved me a lot of time. However, I still have a small problem. Cell A1, A2, and A3 are empty until a currenly value is added. When a currency value is added, cell A4 sums A1, A2, and A3. I want to do the following:

When cells A1, A2 and A3 have no value (empty), do nothing
When the total of cells A1, A2 & A3 is zero – change the row colour to say, red
When the total of cells A1, A2 & A3 is greater than zero, change the row colour to Green

I can do the changing colours ok, but I don't know how to do nothing, because cell A4 is summing A1, A2 and A3 as zero even if they are empty and so changing the row colour to red, I don't want that I want the row coulour not to change to anything until there is a value entered, the value being zero or anything else.

Basically I'm trying to show rows that are dead/lost and those that are still alive.

Claire

Thanks for the great tip – if only the Microsoft Office Assistant 'Cat' was as helpful as you!!

miguel

This is useful if you know the exact cell value to color like 1, TRUE etc, but how can you color a group of rows with same cell value?

ex. 123
123
123
245
245
385
385
385

Bobby

Awesome. Thank yuo so much. I have searched many sites for this simple solution and you were the first one that worked. Thanks again!

andrew

How can I get this to work with multiple condition? I have been trying

=INDIRECT(AND(("I"&ROW())>=1, ("B"&ROW())=0))

and what is really really baffling me now is that if I have a new spreadsheet with only 2 columns and type 2 into B1 and then type =AND("B"&ROW()=2) into C1 it returns false no matter what the value of B1 it would seem…….. if I type ="B"&ROW() into D1 though it prints B1 in the cell so the reference seems to be working………… argghhhhhh!!

Bastien

Hello everybody,

Unfortunately I don't have the time to answer all general (not related to ASAP Utilities) Excel questions.
I use it to give some examples and share some knowledge and give you ideas on how you can approach certain things in Excel.
If you want feedback on other than the given example, please seek for help in the Excel newsgroups or forums you find on the internet.
The following page shows links to interesting resources and forums:
//www.asap-utilities.com/excel-tips-link-websites.php

Kind regards,
Bastien

Ben

Hi,

This is great, having been looking for something like this for ages. I need to expand the criteria, only slightly though.

I basically need:

If Cells = less than 4 then row is green
If Cells = 4 then row is amber
If Cells = greater than 5 then row is red

I would also like the conditional cells to be the result of a sum

=NETWORKDAYS(C2,E2)+1

Can anyone help?

Thanks

ben

andrew

This is a very fantastic tip, i am searching from so many days, can you please tell me if there is any idea on how we can add more conditions ( because i am using 2003).

Sanaz Fatery

I am still not clear how to change a column base on the row… so i wand to highlight any column that the row 13 of it is zero after highliting my colomn A7 to A44, i opened the conditional formatting and i typed
Formula is =indirect("13", column())=0
i think it is totally wrong please help

Gabriela

Hello,

Is there a way to do color code rows per unique number on the colum? (want different colors per specific numbers in each row) ie. gray for 1234, white for 5432, gray for 8901 and so on… Please help….??????

1234
1234
1234
5432
5432
8901
8901
6789
6789
8764
8764

Suma

Great update! This saved a lot of my time !

Dean Stokes

Thanks SO much. Saved my butt helping me with formatting to trap human error at work.
You da man!

Nazim

Hi

I'm using excel 2003. i also need this kind of a code. but its not working for me. why is that ?

Thanks you

Nazim

Jani

wazzzup,

can anyone tell me how to make row change color if i put date (12.12.2009) in cell???
i tried this:

=INDIRECT("C"&ROW())=DATE
=INDIRECT("C"&ROW())=dd/mm/yyyy and similar,
but nothing is working?

Regina

If am working in Excel 2007. I am trying to do a conditional formatting in which if a cell =#N/A then to highlight the entire row. I am having a difficult time with this. I cannot use the "if(iserror)" as this does not work in 2003. I tried If(isna) and nothing. Any suggestions???

Amelia

Hello,
I am trying to change some cells because of other cells. But using colour. I.e. If a certain cell has red writing, then another column of cells will colour red too. Please help! This is a GCSE project.

Caminante

Great Tip – Thank you for taking your time to help us!

Dharmik

Thanks.. This was very helpful.
JIC anyone was struggling like me..
Conditional Formatting -> Manage Rules -> New Rule (or Edit Rule) -> Use a Formula to Determine… in this section type: =Indirect(""&ROW())="""
You can select the part of worksheet you want to apply this to before or after.. if you have a big worksheet in the beginning select a few rows and test before applying to the entire worksheet.

machaggis

Awesome info, helped me greatly. Thanks!

Helen

Hi

can anyone help me with a conditional format problem in Excel 2007 – I want to format a column of values based on another column of values, one colour for under 30, one for 30 to 60 and one for over 60 – I can do the under 30 and over 60 no problem but I have no idea how to do the formula for less than 60 but greater than 30? any help would be appreciated

Sam

I have been looking for this for hours. I needed to add a conditional format to a large column of cells to check the sum of their respective rows but couldn't figure out how to reference the cells without using the excel coordinates in the conditional formatting. I could have created a program in VB with a button to accomplish this, but I really wanted the color change to be automatic as soon as information is entered, not when the macro button is clicked. Thanks.

Marc

Hello can anyone help me.

If in a range (for example R5:T13) a cell is blank how can I get get range A5:T13 to turn blue??

Much thanks

ganesh

Great info… I was looking for this for a long time. Thanks.

Pooja

Wow thanks so much… this really helped me.. :)