Free Microsoft Excel 2013 Quick Reference

Conditional Formatting Excel 2010

I am trying to highlight any cell in column B that is less than the value in the cell in column A.

If B2 is less than A2 highlight, if B3 is less than A3 highlight etc


Post your answer or comment

comments powered by Disqus
I'm using Excel 2010 and I would like to be able to highlight a cells in one column
that have a greater, equal or lesser value to the corresponding cell in the next column(each value having a different highlight Colour e.g (Greater -Red),(Lesser Green) &(Equal-Orange)
I know I can do it with conditional formatting (CF) but do I have to do with CF for each cell individuality.

Can I customize my own set of conditional formatting icon in Excel 2007? If so, how?
thanks in advance.

Thanks in advance to all those who answer.

Before i ask what i need to know i will clarify some things i already know to help out those who answer.

1. I know that you can conditionally format Excel to change the background color if it meets a value.

2. I know that you can change the formatting of a cell in column A based on a value in Column B.

3. I know that you can only set 3 conditions in Excel 2002.

4. I know you can set unlimited conditions in Excel 2007. (i dont want to get 2007)

5. I know that you can use VBA code and apply it in excel 2002 in order to create more conditions.

MY QUESTION IS:how can i get VBA code that can add conditions (more than 3 per column) to a work sheet that allows conditioning of cells in Row B based on values of Cells in Row A.

If you could supply a VBA code that i could copy that would be great.

I am also open to purchasing an excel plug in for 2002 that allows unlimited format conditions.

And an open to any other suggestions you may have.

Thanks for your help.


I'm trying to use Excel 2007's Conditional formatting color scales, but I would like to have the numbers within the cells 'disappear' or match the color of the background. Any advice would be appreciated!


A range of my cells have drop down lists, which have 3 choices: blank (nothing), Customer, Client. the fill colour of some of these cells is grey, others are darker grey, and others are white (this has been done for cosmetic purposes and to make it easier for me to read).

Conditional formatting has been added to all these drop down cells, so that if customer is selected, the font turns "orange", and if client is selected, the font turns "blue". I have set the Background/fill in conditional formatting to "no colour/no fill".

my problem is that it doesn't make the background "No fill" - but in fact white! no matter what i do!

when an actual selection is made (either customer or client), the font changes colour as it should, but background colour changes to white, even though it should still remain grey (or dark grey) considering i set the background to "no fill/no colour"!

for my original fills that are set to white, then isn't a problem - but for my grey cells, it is.

i understand that I could individually set the background of the grey cells to the same colour in conditional formatting to match it, but that is time consuming - and namely; i shouldn't have to, as i have set the background transparent by selecting "no fill".

argh so frustrating!

seems to be an excel bug to me.

anyone else had this problem? help!

In Excel 2007, new feature of conditional formatting is the "icon set" in
which you can assign an icon on a particular value or range of values. i just
what to know how does the default condition of icon set work? by default,
excel is using the 67% and 33% for 3 iconset but i want to know 67% of what
or 33% of what? how did they compute for that? how did they get it or how did
they compute it? i tried several ways of computing it using the 67% and 33%
but still it doesnt fit the default conditional formatting for icon set. is
it the 67% or 33% of the total of the range or what? im really confused. i
really need to know the answer coz for sure my students will ask me about it.
hope someone can help me about this...

thanks! ^_^

Excel 2003 does not allow references to other worksheets for Conditional
Formatting criteria. Does Excel 2007? Thank you, JP.

Hi, hope someone can help.

I created a conditional format formula with ver 97 that hightlights duplicates:


It works fine in 97, but when users use a newer Excel, it only works at an extreme snails pace (many seconds later even when trying to enter new data on the sheet).

Any advise would really help.


On the attached worksheet, I have rows of "Teams" that undergo 3 Games (starting with Game 1 and working right). Each game is divided into an "Open Time" and a "Close Time". I would like to conditionally format the worksheet in that the row is highlighted red up to where the Open Time is entered, and the row is highlighted blue up to where the Close Time is finally entered. I have manually formatted my worksheet in order to better show what I'm looking for.

In previous attempts my formatting only applied to the current cell once I entered values in the Game 2 and 3 columns.

I realize this is an Excel 2007 file, but the actual file will be managed via Excel 2003.

I need to format an entire row based on the text entry in a particular column.

I know how to do it for numbers (using a formula) and I can also apply the formatting to a individual cell. But as far as I can tell, Excel simply doesn't have the capability to apply formatting to an entire row based on matching text.

...or does it?

I did hear rumours about using VBA but I have no idea what that is >.<

In Conditional formatting I am only permitted upto 3 Conditions but I
have 6 conditions how may I overcome.

Do later Excel revisions allow for more conditions in Conditional


Hi there

Firstly, I'll give an image example of my worksheet:

The cell that is coloured with a red fill has three conditional statments in it, which are:

If highlighted cell value is greater than C2 then fill is green
If highlighted cell value is less than C2 then fill is red
If highlighted cell value is equal to C2 then fill is blank

So basically if "6" in this case is lower than "9" then fill the cell red, which it has done. But how do I copy this conditional format to all the other cells, ie: D2, F3, H2, B2, D3 and so forth (the first number of every group on every row)... when I tried to copy + paste the cell to another one I thought that Excel would automatically change the value itself of the number it was look at, but it doesn't and I copy it, it looks like this:

Which is wrong because each of the copied cells is still using C2 for it's original value, instead of the value of the cell to the right of it, so looking like this would be correct (I just manually coloured all the cells for the example):

Is there an easy way to do this, or do I have to manually create hree conditional format rules for every single cell? (which would be very harsh, because the screenshots are just examples and the proper workbook has hundreds of columns)



I am new to this forum. This is what I am struggling with.

Attached is an excel sheet with an example of what I am trying to do.

If value in column A is not equal to the value in Column B, then either Column B or Column C should get the color Red. Can I do by using a formula in Conditional Formatting?

I tried using IF statement but dont know how to pick a color in the formula. I am a newbie, so please help me.


I would be most grateful if anyone could assist me on the correct syntax for an IF/OR statement in Conditional Formatting in Excel 2003.

What I am trying to check for is:-

If E11 >= C11 OR if E22 <= D11 then colour the cell yellow

Any assistance would be greatly appreciated

Many thanks in advance


Okay, so I need to know what I am doing wrong with this Macro Code on MS-Excel 2010. I am very new to this, and it is my first time on this forum. Currently at work, and I simply can't find out what would be the proper solution. There is several sheets I need to update that contain around 1200 rows then AK columns. I have a duplication problem where one row has more then the other row. Can Anyone help? I will be on this forum until this is answered, so please respond immediately. I attached an example of 6 rows extracted. Below is the macro that I was discussing.

Sub Remove_Duplicates_with_Criteria()
EndData = Worksheets(1).Range("A1").End(xlDown).Row

With Worksheets(1).Range("AL2:AL" & EndData)
.FormulaR1C1 = "=IF(RC38>SUMIFS(Criteria,R1,RC1)/IF(COUNTIF(R1,RC1)>1,COUNTIF(R1,RC1),1)," & Chr(34) & "Keep" & Chr(34) & ",IF(COUNTIF(R1,RC1)=1," & Chr(34) & "Keep" & Chr(34) & "," & Chr(34) & "Delete" & Chr(34) & "))"
End With

For rw = 1 To EndData
If Worksheets(1).Range("AL" & rw).Value = "Delete" Then Worksheets(1).Rows(rw).Delete
Next rw

End Sub

I was trying to alter it from the code on this site that looked like this

Sub Remove_Duplicates_with_Criteria()
EndData = Worksheets(1).Range("A1").End(xlDown).Row

With Worksheets(1).Range("G2:G" & EndData)
.FormulaR1C1 = "=IF(RC6>SUMIFS(Criteria,C1,RC1)/IF(COUNTIF(C1,RC1)>1,COUNTIF(C1,RC1),1)," & Chr(34) & "Keep" & Chr(34) & ",IF(COUNTIF(C1,RC1)=1," & Chr(34) & "Keep" & Chr(34) & "," & Chr(34) & "Delete" & Chr(34) & "))"
End With

For rw = 1 To EndData
If Worksheets(1).Range("G" & rw).Value = "Delete" Then Worksheets(1).Rows(rw).Delete
Next rw

End Sub


I am very lost, and I would really appreciate and answer. In case anyone is wondering, this is the thread where I got the code. It answers something very similar to mine, but as a new user it doesn't explain how to properly edit it to fit whatever may be needed in the future.

Hello, new on the forum but really stuck on excel. I'm using Excel 2007 and trying to conditionally format a series of cells to illustrate the percentage distribution of a total.


cell A1:100,000
A4:Conditional formatting to highlight distribution

On the conditional formatting I can only find a graded scale but need the bar to be a solid fill with clear definition of the two values ideally with a midpoint bar.

Any help on this matter is greatly appreciated.



Hi All

Quick question, I want to know how to use conditional formatting to highlight a cell from a certain point onwards?

For example

On my sheet i have an IF statement on cell A2, if it is true then i want CF to fill in the cell A2 and onwards to a specific colour.

How can I use CF to do this

thank you so much


I am working with HR department, i need to make one excel sheet for 200 employees to keep eye on their leaves.

In below excel sheet, i want help here
Can it possible i show names on one single row with conditional formatting.

[Excel Jeanie HTMLSheet1

*BCDEFGHIJKLMNO1NameStart DateEnd DateMon TueWedThuFriMon TueWedThuFriMon 2Clive R Harvey03-Sep-07 04-Sep-07 3-Sep4-Sep5-Sep6-Sep7-Sep10-Sep11-Sep12-Sep13-Sep14-Sep17-Sep3Clive R Harvey17-Sep-07 17-Sep-07 3-Sep4-Sep5-Sep6-Sep7-Sep10-Sep11-Sep12-Sep13-Sep14-Sep17-Sep4Colin B Grover07-Sep-07 07-Sep-07 3-Sep4-Sep5-Sep6-Sep7-Sep10-Sep11-Sep12-Sep13-Sep14-Sep17-Sep5Colin B Grover10-Sep-07 14-Sep-07 3-Sep4-Sep5-Sep6-Sep7-Sep10-Sep11-Sep12-Sep13-Sep14-Sep17-Sep6Colin Starkie03-Sep-07 21-Sep-07 3-Sep4-Sep5-Sep6-Sep7-Sep10-Sep11-Sep12-Sep13-Sep14-Sep17-Sep

Excel tables to the web >> Excel Jeanie HTML 4


Hi All,

I would like to use Conditional Formatting (Excel 2003 Windows) to put a
Border around two cells in the same column. The second cell to be formatted
will always be directly beneath the first cell; i.e. cell I2 (1st) and I3

There are two consecutive rows for each Numeric Label - the Rank is the 1st
row and the Frequency the 2nd row. The Rank is listed twice in column "B" for
each Numeric Label for sorting purposes.
Each Rank in a row is unique.

The criteria to Conditionally Format the cells - Format Rank and related
1. Match Rank >=10 (greater than or equal to 10) in the 1st row of the
relevant Numeric Label - CF is a Border. The Ranks to be matched may be in
any column between "E" and "I".

2. Put a Border around the cell in the 2nd row (Frequency) that corresponds
to the Numeric Label and is directly below the Matched Rank in the 1st row.

Sample Data Layout:
Columns: "A" = Numeric Label, "B" = Rank, "C" = Total, "D" = Text Labels, "E":
"I" = Rank & Frequency (6th-10th Position). The Data starts on row 2 with
Numeric Label 220. Data row 2 to 11.

Label Rank Total POS 6th 7th 8th 9th 10th
220 18 Total Rank 14 10 17 12 18
220 18 1041 Freq 7 6 5 3 3
470 16 Total Rank 6 15 13 17 11
470 16 1058 Freq 4 4 3 3 2
180 14 Total Rank 13 10 16 12 14
180 14 1042 Freq 6 5 4 3 3
400 13 Total Rank 11 14 5 23 13
400 13 1053 Freq 5 5 4 4 3
40 11 Total Rank 9 11 13 15 14
40 11 1040 Freq 6 5 4 4 3

Expected Results:
Label 220 - Row2 Column "I" Rank 18 & Row3 Frequency 3 should have an outline
Label 180 - Row6 Column "I" Rank 14 & Row7 Frequency 3 should have an outline
Label 400 - Row8 Column "I" Rank 13 & Row9 Frequency 3 should have an outline
Label 40 - Row10 Column "F" Rank 11 & Row11 Frequency 5 should have an
outline Border.


Message posted via

Just installed 2007 and like it - would like to use conditional
formatting to highlight cells based on if the cells equal a group of
numbers - actually it is a worksheet containg all the office pool
powerball tickets so I want to go through the worksheet and highlight
the winning numbers - so I need to be able to have something that says
if a cell in the range = 1 or 2 or 3 or 4 or 5 etc. Can it be done?


Hi, I have values in a cell that changes if the cell next to it changes.
The values are five constant values, i.e. "Critical Criteria", "Non-Critical
Criteria" etc.
I need to colour the cell a different colour based on each unique value.

The conditional formatting only caters for 3 conditions. How do I do this. I
do not have knowledge of using macros.

Appreciate it.

Is there a way to use conditional formatting on a text field with leading
For example, I have a department number of 00123 and would like to highlight
all instances of this number. When entering directly in to the dialog box,
the zeros are dropped.

Any ideas how to do the following using conditional formatting (or is it even possible?) -

I have a range of values from 0 - 100
I want to use conditional formatting to make :
values that equal 0, cells turn the colour green
values that are between 1 and 99, cells turn the colour amber
values that are greater than 100, cells turn the colour red

For examle : -
Condition 1 Formula Is = A2 = 0
Condition 2 Formula Is = IF(AND(A2>0,A2<100)
Condition 3 Formula Is = A2 > 100

Hope the above makes sense - any suggestions would be appreciated!!

I have a spreadsheet I created in Excel 2003 that compares 4 different values and highlights the lowest value by turning the cell background green. It works fine in that version. I just upgraded to Excel 2010 and found that the function no longer worked as before. All cells are green to start. Entering values in each of the cells turns the cell to the normal background. However, the lowest value does not turn the cell back to green. By opening the Conditional Formating button on the ribbon and unchecking the "stop if true" checkbox and then hitting the apply button, the proper cell is highlighted. I then tried unchecking and immediately rechecking the same box (back to the original format) and then hitting the apply button and it worked again. It appears that an activated "apply" button refreshes the function and the proper cell responds. What is keeping the function from automatically performing when the data is entered prior? Code is as follows [=$E48=MIN(IF(ISNUMBER($E$48:$E$54),$E$48:$E$54))] Resulting action is the green cell background for the lowest value. Thank you.

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