Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Whole Line Conditional Formatting

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e

Similar Topics

Hi all, am hoping someone can help with a excel formatting issue.

I need to set up some conditional formatting on my spreadsheet, however I am having a little trouble with one of my conditions.

The requirement is:
- if a cell value in column X contains the word "open", format row background to yellow.
- if a cell value in column X contains the word "resolved", format row background to green.
- if a cell value in column X contains the word "moved" OR "closed", format row background to blue.

Now I have manged the first two on my own, using the conditional formatting tool and using the formula "=SEARCH("open",INDIRECT("X"&ROW()))".

However I am stuck on the last one. I tried...

=OR(SEARCH("resolved",INDIRECT("X"&ROW())), SEARCH("closed",INDIRECT("X"&ROW())))

However this doesnt work. I tried looking at adding VB script in but to be honest I am not a VB programmer and cant really spend too much time on this. Any suggestions to fix the final conditional format so it run if the cell contains either "resolved" or "closed"?

Thanks for any and all help.


I have a main spread sheet which has cells that change background colour via condition formatting and I would like to count the different colour cells in a column. The main spreadsheet is linked to other worksheets and its the data on them which triggers the conditional formatting. I have researched the forums etc on how to count conditionally formatted background colours in a column and none of their solutions seem to work. In an attempt to find out what is wrong I have run a program which shows the numeric value of the cell according to its background colour on the main sheet, and the value doesnt change when the conditional format changes the cell colour. The only thing I can think of is that because the data is pulled from another worksheet somehow this is messing up the "normal" conditional formatting count formulas I have found. I have read that you can use the formula which triggers the conditional format with countif but being very inexperienced i have not been able to figure this out. This is the conditional formatting formula =if (and(I11="m"),today()-O$7>N11) Sorry its long winded but can anybody help me with this please?

I've searched the forums and found many discussions on conditional formatting, but none quite what I'm looking least to my untrained eye.

I have a spreadsheet with text data in A2:L13
I want to to color the background of the cells based on the text in each cell.

For instance,
cell = "Branch" then color background red
cell = "Division" then color background blue

I need up to 12 different conditions. I believe I need VBA to accomplish this, what with the 3 condition limit in plain ol conditional formatting.

If anyone can help, I would be very grateful.

(And if you type in code, a little explanation of what the code is doing would be helpful for me. I'm a newbie and still trying to figure this stuff out).

Thanks in advance!

1. I want to change the colour of a cell if another cell contains any text.
For example, if cell A1 is empty then cell A2 will be standard white colour. If cell A1 contains any text (any word/letter/number), then cell A2 will change to a specific background colour.
Can this be done using conditional formatting or function formulas, as I want more than one cell to change colour and have different border types.

What I want is to create a new row (with different colour, borders, formulas and conditional formatting) for a database once a certain field is filled in.

2. Using "#,##0;[Red]-#,##0" to custom format cells, how do change the colour of the cell background rather than the text colour.

Any help is appreciated, thanks.

I am using excel 2003. I have already used my 3 conditional formatting options, however I now have two problems.

1. Current conditional formating will change the background depending on a name entered in column H, I want the background of the row A- AE to change not just the individual cell.

2 Secondly, I would like the text of the entire row to change depending on the value in column M.

If the text in column M is "Bound" then I would like the text to be blue
If it says "Lapse" then I would like the text in the row to be red and if the value is anything else I would like it to remain black.

Can this be done using an IF formula as I have no conditional formating options available.


Hello All,

This may have been covered by previous topics, but I could not see it.

I want to apply 3 different conditional formats to a column where the
cells have a formula applied as follows:

IF(Q11>=3,"3",IF(Q11>=2,"4",IF(Q11<2,"5"))))), Q being a column where a
SUM is calculated.

The conditions I want are RED for value >=5, ("1"); ORANGE for value
>=4, ("2"); and YELLOW for value >=3, ("3").

When I apply the conditional formatting it only appears to work where I
use 'Formula is'. And then it will only apply one condition, i.e. the
higher value. I have tried using 'Cell value is' and putting in both
the presented value, i.e. within the quote marks of the IF argument,
and the calcualted value, i.e. the value range from the SUM column.

Any ideas would be greatly appreciated



i have a set of conditional formatting set up for my workbook, the first is simply that if the result is false the cell is blank(white text,white background). the second if positive result,keeps the text black till 1 year has passed. the problem is the third, i.m trying to allow the user to blank out the cell within the year (white text,white background)if certain criteria are met. the idea is that they change the entry in cell D from T to TX or from M to MX and the formula pick this up, but i keep getting a error saying "your formula contains an error", i cant see what i.m doing wrong and i have tried searching on google to no effect, so can anyone help?

the formula im using is, =if(or($D5="tx","mx")) then i set the format to (white text,white background)

its probably really simple but i,ve been trying to solve this now for around 8 hours and i.m stuck

I have a drop down Data list of:

- N/A
- 5
- 4
- 3
- 2
- 1

For instance "N/A" will be the default cell option which I would like to always have the background of gold. If "1" or "2" is selected then it would have a red background, if "3" is selected then it would have an Amber background and if "4" or "5" is selected it would have a green background.

I am running into difficulties as conditional formatting option gives only 3 options which means I unable to conditional format "N/A" to have the required Gold default background preset or if selected again after another option has been chosen.

I would be exceptionally grateful if anyone could offer a solution to my problem.

Thanks in advance.

Hi all
Firstly Happy easter

Can anyone help me with this?

I would like to use Conditional formatting for a cell.
In the cell the contition would be the word "MOT"
This bit i am fine with, the problem im having is users are putting text like "failed mot" or "due mot" and this does not meet the condition.

What i would like to do is Conditional format the cell to be, if the word "mot" appears any where in the line of text in the cell then the condition would be met.

Hope this explains it


This is a followup question to one I posted a few days ago with the
title "How to change cell color based on content". Someone had
recommended I use "Conditional Formatting." So, I tried but it didn't
work out the way I expected.

Here's what I did:

New spreadsheet
in cell A1: Check
in cell B1: 100
Select cell B1
Menu select Format/Conditional Formatting...
Left combo, select "Formula Is"
In formula box: =(B1 > 0) & (A1 = "Check")
Click on Format button
Click on Patterns tab
Click on the red color
Click on OK
Click on OK (in Conditional Formatting dialog)

But Cell B1 did not change appearance

I expected it would change cell B1 to a red background.

What am I doing wrong?



Can anyone help me with the following problem I have with my spreadsheet. I am using conditional formatting to alter the background of cells depending on their function:

I am having a problem with the following, when the current location is set to TCI the background of that cell changes to blue as expected however the background of "Seen by" and Clerked by medics" both remain red and contain text. I am looking for a simple automated way (without macro) of clearing those when location is set to TCI; however if other locations are used I want the formatting for two 2 cells described above to remain.

Any help is appreciated



I would like some help writing a macro to apply conditional formatting.


Not whole page ... apply to selection B55-M55 down

If cell = "XYZ"
Change background to yellow

If cell = "XYZ"
Change adjacent cell background to yellow
eg: Cell C55 = "XYZ", Cell B55 would be yellow

If cell = "XYZ"
Change entire row (Between column B-M)
Change font colour to grey
Change font to italic
Change text alignment to right hand side (I've been able to achieve everything except for this using conventional conditional formatting ...)

If cell = "XYZ"
Change entire row = border / grid lines

Thanks in advance

This seems like it should be so simple to do but I have not had any luck so far.

The entries are made in Columns A thru C. In Columns F and G I want any value over 100% to have a red background, any value less than 100% to have regular formatting, and I'd want percentages to have white font if an employee is deleted from Column A.

Here's an example of how I have the formatting set up:
Condition 1: Formula is =$E$2="" --->white font
Condition 2: Cell value is >1 ---->red background
Condition 3 Cell value is </= 1 ---->normal format

But the first condition does not seem to work. Anyone see what I'm doing wrong?


I have a spreadsheet where I want to change the background colour of the whole row to gray if the text in column L says "Complete". This needs to be done from row 18 to row 201 at present, but more rows may be inserted at a later date and the same formatting would need to apply.

Normally, I would use conditional formatting to do this, but my problem is that columns A, I and J already have the maximum 3 conditional formats applied to change their background colour based on the date in column I. (Note if column L says "complete", then none of the 3 conditional formats is being activated.)

I have read that you can use VBA to add more conditional formats but can not find the correct code to do what I need it to do (and explain it in simple terms!).

Can anyone help me?

I have a spreadsheet that I previously was using conditional formatting and datedif to evaluate and change the background color based on a formula.
My problem is that they want me to change from 3 validation checks/colors to four and standard conditional formatting only allows three.
(Example for my G6 cell)

I have various deadlines and I have formatted my cells to have a Green background if the deadline is met. So the first condition is "Less than or Equal to." The cell turns red if the deadline isn't met, so the condition is "Greater than."

I would type in 12:51 as the conditional value and not worry about a.m or p.m. The numbers are converted to something like 0.03546666.

Things have been going along great until I got into early a.m. times, but now I'm having problems. If I type in 12:51 as the conditional value and 11:20 as the actual I should get a green background, but I'm getting red.

It doesn't seem as if AM or PM should make a difference in the outcome, but something strange is going on. I tried formatting the actual time as PM, but that didn't make a difference.

Thanks for any help.

Hi guys,

Need some help on formatting (coloring in yellow) 4 sets of ranges. Two of which are the same. Eg: If any cell in column A = "Create" or "Amend", then color the background of cell ranges A5:D5 & G5:N5. If any cell in column A = "Failure", then color the background of cell range A5:N5. I want this formatting to apply to the first 100 rows.

I would like to use VBA but only know VERY little on it.

I have tried conditional formatting which works for one row but when I copy over to the rest of the rows (having set it up on row A initially) it doesn't seem to work and colours the whole line as opposed to just the selected ranges/cells.

i was using the following and selecting the various required ranges before going into conditional formatting:

1st Condition: =OR(A$5="Create","Amend")
2nd Condition: =A$5="Failure"
3rd Condition: =A$5="Delete"



I have two columns of numbers where the totals should be equal. To call attention to any problems, I set up conditional formatting to turn the background of each cell to red if they aren't equal. Conditional formatting is set up in cells B9 and D29 with the formula =B9D29. Problem is, the cell values are equal and D29 has a red background; B9 is clear.

I've already double-checked to make sure both cells have no fill set for the background color. I've also manually entered the amounts in both cells. Any ideas?

Current Month 21,705,075.74 57,101,751.65 131,939,491.99 15,355,300.14 7,411,779.09 102,391.45 $233,615,790.06 Amounts 68,181,263.00 10,511,831.49 45,507,555.65 3,051,818.03 2,853,581.47 5,766,863.56 1,951,431.78 20,034,471.31 13,145,888.63 8,748,173.32 2,122,735.14 33,699,151.56 9,234,863.37 8,703,821.30 102,391.45 (51.00) $233,615,790.06


I am entering a value between 1/10 and 10/10 and sometimes the field is blank.

I have conditional formatting setup as follows:

Condition 1: [Cell Value is] [Less Than] ="5/10" (Displays a red background)
Condition 2: [Cell Value is] [Greater Than or Equal to] ="5/10" (Displays a green background)
Condition 3: How can I set this so if the field is blank, the background is white?

If you could help me I would be very grateful.

I have a row cells, format set to set to webdings, so that when I type letter a I get a tick in the box. Using conditional formatting i change font colour and background colours. Ok my question ...

I type the letter a in one of the boxes and it automatically assumes I wnat to enter the word "around". I have enclosed a snippet from the sheet.

Any ideas why?

Simply put, I need to create conditional formatting so that after one year the color of cell changes - based on the date in another cell. The reference date is in cell E4.

Cell E2 = Today
Cell E4 = 8/26/09

This is what I have currently - it should change color on 8/26/10. It doesn't change until I change year to 2011.


What am I doing wrong?

I have a conditional formatting probelm that should be easy but I can;'t work out!

Cell j19 = 8,586.84
Cell j20 = 1,369.50

Cell L23 = the sum of the two i.e. 9,956.35

If J20 + J21 = L23, I want the the background to turn green (I do not want to use a sum for these calculations!)

I am using the 'formula is' option in condition formatting with the following syntax: =(J19+J20)=L23 and have selected greeen as the pattern, but the cell is not doing anything!

Could it be because the values in j19, j20 and l23 are formula driven? If so, does anyone have any suggestions as to how to get round this?


For a certain range of cells, I need to be able to run multiple conditional formatting and validating prior to a save/export. The basics for it are as follows:

Check if the current cell is empty
If the cell is empty, then no formatting

Check if 2 other cells are empty
If both cells C7 and B11 are not empty, C11 should not be empty so change cell background to amber. Otherwise no formatting

Check contents of current cell - should be either A, B, or C. If none of these, change cell background to red

I might be going about this the wrong way, but I've tried in conditional formatting as I would normally, including changing the order of validation, but I can't get all of these to work. C11 in this circumstance would be the cell that changes every iteration and C7, B11 would be static for each column, then shift across to the next column.

If I need to be clearer, please just let me know - I'd imagine I probably have made it sound more muddled than it is!!

Many thanks in advance for any ideas. I was thinking along the lines of VBA code using a beforesave loop...


okay, I have a sheet with a bunch of skus, ie "MCREADER". If i have none in stock, I want it to be red. I will indicate that I have none in stock by putting a zero as the first character. So the sku would then be "0 MCREADER".

Now, I select that cell "C11" in this case. Go up to conditional formatting. And use the formula:


="IF(MID(C11, 1, 1) = 0)"

Select the background to be red.

But nothing happens. Is there something wrong with my formula.

Also, how would apply this to all the cells on the sheet. How do i copy & paste the conditional formatting to apply for all the cells, that i set up for that one cell.

If there's a better way to do this, please let me know.
thanks for any help in advance.


That formula is real close but isn't quite working correctly. I tried it and
in my table i put about 10 city abbr. Then in my named range I type "HOU"
but for some reason after I did that the cells with "hou" turn red as was
supposed to happen but so did the cells with "WAS", and "PIT". If I put in
"DAL" into my named range the cells in my table with "DAL" turn red but so do
the cells with "WAS", "PIT", and "DEN". So for some reason it is formatting
the correct cells but also identifying others as needing formatting. Formula
probably just needs a small correction but this formula is above my skill
Please Help.....and Thanks!!!!


"Rowan" wrote:

Select A1:D10 so that A1 is the activecell (should be white wiht A1
shown in the Name box on the formula bar). Then apply the conditional >
formatting using the formula:
where Red refers to your named range holding the abbreviations of cities
you want coloured red.

Hope this helps

"Guenzak" wrote:

Thanks Vac but that isn't quite doing it. I am applying the conditional
format to colums ABCD rows 1 thru 10. What I applied was
What I wanted to have happen is if "HOU" appeard in any cell H1 thru H10
then all of the cells in my main table (ABCD 1-10) that had "HOU" in it would
format i'ts background to red but it isn't quite working that way. Also I
have 40 different city abbr. so I have to replace "HOU" in the formula with
something that would represent "anything" so that I can use any of the 40
abbr. in cells H1:H10 to change the background of the cells with those cities
in the main table red. So if H1 had "CAR" H2 had "BUF" H3 had "DAL" then all
of the cells in my main table that had either CAR, BUF, or DAL would format
to a red background while all of the other cells with other cities remain

you see my real table has over 400 cell with 40 different cities and a few
times a week I have to highlight different cities different colors. To change
the cell backgrounds manually takes hours. I want to be able to write the
abbr such as "DAL" in a specified column to the right of my table (that
column being H1 truH10 in the example above) and have all the cells in my 400
cell table that have "DAL" in them to change red.

Thanks for any and all help!!!


"Vacation's Over" wrote:

try countif
countif(yourrow say A5:h5, abbr say "HOU")>0

conditionalformat formula is -

"Guenzak" wrote:

I have done this before and can't remember the formula or figure it out
again. I have a table the say is 10cells X 10 cells. In the table I want to
put the abbr. for cities such as DAL, HOU, DEN. Now next to the chart I have
10 cells that I highlighted and named RED. In the past I had set up a
conditional formatting formula so that if the abbr HOU in in one of the 10
cells that were called red all of the cells in the
table that contained the abbr HOU would format its backround to red. It was
a formula that was set up as a conditional format for the 10x10 table and in
the formula it referenced the name of the group of cells that I highlighted
and renamed red. Can anyone here help me with this formula? This is the same
board where I found the formula a year or so ago but now I can't find it or
remember it
> Thanks
> --
> Guenzak