Free Microsoft Excel 2013 Quick Reference

Whole Row Color Change - Conditional Formatting

OK, so this is my first post. I've been using Excel for awile but am fairly new to VBA.

I'm trying to make a tracking sheet of upcoming annual inspections for different pieces of equipment. I've already got a column set up listing the inspection due date. Using three conditional formats, the individual cells change color based on the amount of time until the inspection is due (green normally, yellow at 30 days out, red past due).

What I'd like to do is have the entire row of information change color based on the color of the cell the inpection due date is listed in. For ex. J6 lists the inspection date as 24-Feb-07 and is therefore red, I'd like A6-I6 and K6-P6 to change to red as well. And when the inspection is compleate and I manually change J6 to say 25-May-07, I'd like all of row 6 to change green based on the fact that J6 is already going to.

Any help anyone can give me would be greatly appreciated.


Post your answer or comment

comments powered by Disqus
I got advice from this forum to change color of entire row by use conditional
format function. The advice is that "at first select entire row, then use
condinoal format". I have follow the advice but don't get result or it's not
change color of entire row, only conditional format cell address is changing
the color. If some one write me clearly about the formula or format, I shall
be very thank full to him or her.


Can any 1 help me wid code for counting colors with conditional formatting.

Have tried a no of codes but doesnt work...It works fine wid sheets without any conditional formatting.
Have tried C Pearsons code as well, but no luck.

Thanks in Advance,


I got the row of cells to do what I want by acquiring all the cells in the row and writing a conditional format.
Now I know how to copy cells using the little plus sign, but it does not work when I try it with the whole row/copy. hte conditional format just doesn't come through.
Am I missing a step?

Thanks again,

While there are lot more colors with conditional formatting in Excel 2007, I
am seeing it function less than what it had before, so looking for some help.

I have several rows of data with several columns. The last column is a
postiive or negative number depending on the performance of he item covered
in that row. If negative (or maybe even less than -25), I want to set the
whole row (or at least the cells I am covering) to all be one color (e.g.

Assume data is covered in columns A to L and L is the one with the number to
which I want to refer. Each row is unique, so I do not want to apply results
for row 2 to row 3 or anything else. Just L2 to row 2, L3 to row 3, L4 to
row 4, etc.

When I try this it seem to always want to refer to just one cell or will not
apply to whole row. Had not problem in the past, but if I try moving file in
to Excel 2007, it will not take this if I try to make any changes.

I have checked the manual and not found anything to resolve this, so help
would be appreciated. Just problem in Excel 2007.

I use the rules in the conditional formatting tab and choose that if text in cell is 'completed' change to x color and it works fine. But I need the whole row to change color based on input in one cell doesn't seem to help if a highlight row while setting up rule either..

I have some data in A1:G10, & F1:F10 is validate cell (List), i.e.
"Reject","Accepted","Pending", now i want to change the color of whole row if
I select "Reject" or "Accepted" or etc. in F column.
I have made a C.F. i.e - Formula is =$f$1="Rejected", format patern is red
color, Formula is =$f$1="Accepted", format patern is green color, Formula
is=$f$1="Pending", format patern is yellow.
My problem is that when I select "Reject" or "Accepted" or "Pending" then
only the F1 cell is being color not whole row. What should i do ?

Hello everyone. I'm having a bit of a problem with color formatting in Excel 2003 lately.

I did a search for this topic, but all I found were references to "searching" for cells colored a certain way, or sum/average/locate cells which were colored. I couldn't find anything that said something about changing the actual text itself based on a set of criteria.

My situation is this...

In cell A1, I have a drop-down list from Data Validation. When a specific selection is made in the list, cell B1 pops-up with a text entry based on a VLOOKUP which checks cell A1. For simplicity sake, I will use simple letters for these text entries. For example, if the 1st item in the drop-down is selected, the letter "A" will appear in cell B1 and I need it to be RED. If I pick the 2nd item in the drop-down, I need the letter "B" to appear and be BLUE. This continues for 6 different entries, all of which being a different text and color at the same time. If I go back and change the drop-down selection, I need the color of that text to change accordingly. I've actually got close to 30 consecutive rows worth of Drop-Down lists, each one corresponding to cell in the next column. I could really care less what the colors actually are, as long as they are readable on a white background. In fact, if the font colors themselves are the problem, I'll even go with changing the background at this point.

I've tried Conditional Formatting already. The 3 limit doesn't allow me to do what I need to do, which is to have 6 different colors used for the text that is inside the cell. I'm aware of the Format > Cells > Number Tab for Custom selections, but that only seems to work for text in general, not for specific text. Since the entries in B1 are not numbers, I can't use the built-in [Red] and [Blue] etc. for the custom formats. If anyone knows of a way I can specify a color based on specific text entries, please let me know. Perhaps a combination of Conditional Formatting 3 of those colors and something else entirely different can solve the problem.

I already have my lookup function in cell B1, but if there is some other color-based function that I can add to it...that would be great. Something like: IF(A1=1,"Text is Red",IF(A1=2,"Text is Blue"... all the way to 6. I know I can fit up to 7 IF statements, so that wouldn't be a problem, but I haven't found anything anywhere which has this.

Basically, I need a way to automatically change the text 1 of 6 colors based on the specific text entry. I would be more than happy to explain this situation in further detail, but I think I've gone on far enough as it is. Thanks to anyone who can offer assistance.


Can someone please tell me what is the formula to setup in Conditional Formatting for this:

Within only Column A, cell change to color yellow if it is not blank. Everything blank cell leave it unchange.

Thank you!

I have a question about conditional formatting. I have cells beginning with
the text "no exceptions noted", and others beginning with the text
"exceptions noted". For "no exceptions noted" I would like conditional
formatting to change the cell to a red background color and, for the other, a
green background color. I tried using conditional formating with cell value
equal to each of the texts (and changing the result to the appropriate
background color) but it didn't work.
Any suggestions?


I have a row with data spanning 6 columns. Each Col is formatted differently
(some as date, text, or numbers.)

How do I change the entire row to a color if the entry in Col A (formatted
as text) is equal to X? (If I try to copy Col A’s format across the row, it
changes the formatting in the other Cols all to text.)


I know that I can use conditional formatting to change the color of a cell,
but this can prove cumbersome for multiple cells and multiple values.

Is there a way to change the color of a cell based on a formula in that cell?

For example, assume I have cell A1 = 10 and plan on entering a list of data.
I want to compare each value to cell A1 and change the color accordingly.

I would assume the code would then be as follows:

Have a spreadsheet with many lines on it.

We have one cell (starting on row 2, column I) the cell will have one
of 8 different values. (I, O, C, T, L, E, X, A).
Depending on which one, would like that row to be set to change
background color depending on value in cell I2, i3, etc. as you go

Base color is blank (white).

I know I cannot use conditional formatting as I have more than 3
Any idea what the macro would look like?

thx all.


I'm sure the answer to this is "conditional formatting", but I don't know how
to get it to work...

I have a spreadsheet I use to track employee training on procedures. One
column lists the date the procedure is active and another column lists the
date an employees training on it. When the procedure changes I update the
appropriate cell in the first column to show the new date. I want the cell
containing the employee training date to change color if the dates don't
match. How would I do that?

Hello all!
I have an excel sheet that has a column that I fill out with "yes" or "no". what I want my excel sheet to do is when I type in yes, I want the whole row to change to the color red. when it is a no, then I want it to stay white or no fill. How can I achive this?



I am creating a band song list spreadsheet in Excel 2007. The sheet contains one song per row with a status column per song that has a pull-down menu via validated data list of "Ready for Show", "Practicing", "Sidelined". I am using conditional formatting to fill the cell with colors of green (ready), yellow (practicing), red (sidelined). I defined that conditional formatting on one cell in row 5 (which works fine), and then copied the conditional formatting to the other rows. The problem is, when I change the status of the original cell/row where the conditional formatting was defined to "Ready for Show", it changes to green (correctly), but it also changes the colors of all other rows to green (incorrectly, since I only intend it to turn green when each row's status is set to "Ready for Show").

The formula in the conditional formatting where I created it on Row 5 is: =COUNTIF($D$5, "*Ready*")
When I look at the conditional formatting copied to row 10, it is: =COUNTIF($D$10, "*Ready*")

So it looks like Row 10 should operate independently as I intend, but the color for row 10 still changes color incorrectly according to the contents of Row 5 rather than Row 10.

I'm sure there's something I'm missing, but I've been struggling with this for a while so I'm hoping someone can help.


Happy new year all.... new year... new problems!

I have a spreadsheet where column H can be either yes or no. When column H changes to Yes I want the background of the whole row to change to Red.

e.g. Cell H4 contains the word no. I then change this so that it says "yes". I want the background of row 4 to go red. so A4, b4, c4, d4, e4, h4 all go red.

With the type of conditional formatting i normally do i can only get cell H4 to go Red by putting cell value = yes as the condition. I cant for the life of me think of a formula or way round of doing this. Any ideas would be welcome

Hey everyone, this is my very first post here.

I'm working on a little stock database for a school project. I have a conditional format working in one column (A), it puts a certain color in the cell based on the first three letters of what's written in that cell. Now, is there any way I can put that same color in the whole row that cell belongs to? So let's say, A2 is set up with a conditional format which changes the background to green if the text of that cell starts with "AJM". Now, I want A2:G2 to get that same format automatically. It won't work if I just put the whole range with the conditional format because none of the values in that range except A2 starts with "AJM". What should I do?

I have several rows of text with alternate rows coloured blue.

Column A has a date and columns B and C contain text data.

Is there a way to have those alternate rows change colour when the
date changes in Column A?

Below is the text in this new spreadsheet so far, to illustrate. I
have returned or will return library items as per the dates below.
But it would be nice to have the spreadsheet's conditional formatting
change when the date changes in Column A. I manually have the entire
spreadsheet set for blue&white but I realized when I printed out the
sheet today to take with me to the library, that it'll be easier to
have the rows change to one other colour combination so that I have
blocks of blue&white, then, say, yellow+white, blue&white,
yellow&white, etc., down the page. Is it possible to do something
like this programmatically?

Jul.14.2006 CD soundtrack Empire Strikes Back.
Jul.14.2006 CD audiobook Positive Attitude Training.
Jul.15.2006 CD audiobook 1 Minute Millionaire
Jul.15.2006 CD audiobook 7 Steps to Fearless Speaking
Jul.15.2006 CD audiobook Animals, Inc.
Jul.15.2006 CD audiobook The Success Principles


here is a formula that is in use..

IF( INDIRECT("'Roster1'!A1'")=""," ",INDIRECT("'Roster1'!A1") )

so.. if A1 is null return "space" else copy A1

I want to add now..

if A1 = "STR" turn this row green
if A1 = "LTR" turn this row blue
if A1 = "SGE" turn this row red
leave it, or turn it black

i looked at conditional formatting.. however not only could i not get it to work, it appears to me it only looks at 1 cell and cant change the whole row color.
let me know if u have any ideas

I need help figuring out how to change the colors of the arrows as part of these conditional formats using excel 2007. any help would be greatly appreciated...please see attached.

Hi all

I am trying to do color scale conditional formatting.

I select the three cells in the same row, then i go to conditional formating -> color scale ->green-yellow-red scale

But when I want to do it for 200 rows (3 cells each), the color scale is not based on a per-row condition. Instead it does it for the whole selection.

Is there a way to do this quickly?

note: these cells are on a pivot table

I have some cells in a worksheet that have the font color changed by a conditional format. How can I find out the font color of these cells? I need to count the number of cells that are red.

I am having difficulties in getting this right. I would like to color band rows depending on 2 conditions matching ie; Column A and Column F match the row(s) below.
The list is always sorted by column A then by Column F.

The attachment shows what the result should be

As you can see by the attachment, it is not always 2 rows that match.
Conditional formatting or VBA is fine.

Thank you for your assistance.

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL7=
ABCDEF1CUSTOMER CITYCLIENTSRMS#LOGGED DATELOGGED TIMECOMPLETION DATE2COLLIE              DELL7340610625/08/089:4908/09/083COLLIE              DELL7351616201/09/087:1108/09/084DENILIQUIN          DELL7366477710/09/0814:0616/09/085DENILIQUIN          DELL7370788412/09/0816:1716/09/086MANSFIELD           DELL7353094502/09/0813:4008/09/087MANSFIELD           DELL7359723005/09/0816:4808/09/088MARYBOROUGH         DELL7364397809/09/0817:1912/09/089MARYBOROUGH         DELL7368759511/09/0816:3712/09/0810ORANGE              NEC7325400114/08/0815:5408/09/0811ORANGE              DELL7338617722/08/0817:1208/09/0812ORANGE              DELL7338616022/08/0817:1008/09/0813ORANGE              NEC7325382514/08/0815:2508/09/0814ORANGE              NEC7359510505/09/0811:0119/09/0815ORANGE              NEC7368583211/09/0811:3119/09/0816ORANGE              NEC7368581111/09/0811:2819/09/0817ORANGE              NEC7377254217/09/0817:1219/09/0818PORT DOUGLASDELL7359675105/09/0815:4308/09/0819PORT DOUGLASDELL7359669705/09/0815:3608/09/0820PORT DOUGLASDELL7364058109/09/089:1511/09/0821PORT DOUGLASDELL7366418310/09/0812:1511/09/0822TAREE               NEC7372917615/09/0812:3017/09/0823TAREE               NEC7372847115/09/089:1417/09/0824TOOGOOLAWAH         DELL7361970308/09/0814:5911/09/0825TOOGOOLAWAH         DELL7361970908/09/0814:5911/09/0826WARWICK             DELL7388202224/09/0815:0025/09/0827WARWICK             DELL7386043023/09/0818:2625/09/08Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

Say on sheet 1 cell a1, I reference sheet 2 cell b2. Is there any way to set up a conditional formatting so that if the value in cell b2 changed since the file was last saved, sheet 1 cell a1 would change color or something?


No luck finding an answer? You could always try Google.