Free Microsoft Excel 2013 Quick Reference

Conditional formatting/ comparing worksheets

I need to compare data from 2 worksheets. There are three rows of data I
need to compare in each worksheet:
WS - A

WS - B

I need to compare WS-A, row B,D,F with WS-B row B,D,F and store the
different in a third worksheet and/or apply conditional formatting to both
worksheets. I would appreciate any help. Thanks.....

Post your answer or comment

comments powered by Disqus
I have 2 worksheets - one is a monthly update, the other is last month's data.

I want to highlight the changes on the monthly update sheet using conditional formatting.

I named the columns as ranges on the "last month" worksheet, and then used =Match(A1,Jan,0) (wheras Jan is the range on the "last month" worksheet where A1 resides)

What formula do you use to say "if A1 does NOT match a value in range "jan", then format it"?

Is there a way to do a conditional formating comparing the value in one cell to another. I would like to compare Cell A1 to A2 and if they are not equal, color A1 Bright Yellow.

I have
Cell value is
Not equal to
***but I do not know how to state Cell A2

The value in A1 is constant. The value in A2 is variable.

I have got stumped and would appreciate some help with this please.

I need to highlight cells where the batch numbers are the same as recorded in previous sheets.

Batch numbers are identified by the first 5 digits.
I want to look at a previous worksheet, or if possible worksheets, in the current workbook and see if there are any matches. If this occurs activate the conditional format pattern I choose.

In the Conditional Format criteria box I've got as far as Left (A12,5) =
But can't work out how to stimpulate how to search through a range of values in the previous sheet (A2 to A9 in sheet named G9 Data WkCom 170907).


Hi All,

I have two worksheets using numeric values:

Sheet1 Range N17:N100 (84 Rows)
Sheet1 Range K17:K100 (84 Rows) - If condition is met apply Strikethrough
Conditional Format
Sheet2 Range K197:K280 (84 Rows) - Named Range "YR2006"

I would like a Conditional Formula to set criteria below and apply CF:
IF a cell value in Sheet1 N17:N100 is >0 (greater than zero) AND >= (greater
than or equal to) its corresponding cell value in Sheet2 Named Range "YR2006".

The cells in Sheet1 Range N17:N100 should be compared individually to their
corresponding cell in Sheet2; ie. Sheet1 N17 is compared to Sheet2 K197,
Sheet1 N18 is compared to Sheet2 K198 and so on.


Message posted via

I want to create a conditional format in one worksheets, so when the value in another worksheet changes, the cell in the current worksheets changes color. Excel says this can't be done. Is there a workaround? Thanks!

I am a market analyst and am using Excel 97 to record my findings. In my spreadsheets, I record the following data: Our pricing, competitors' pricing, and comparisons which yield the average of all competitor pricing, our amount in $$ above or below the average, and our percent above or below the average.

Formatting I use:

Percent/$$ above average: bold, red
Percent/$$ below average: bold. blue
Percent/$$ equal to average (or 0): bold, black
Individual competitor price $1 to $4 below our price: light green background
Individual competitor price $5 to $9 below our price: tan background
Individual competitor price $10 + below our price: light yellow background

Now, I believe I can handle the percent/$$ formatting with a simple "Cell value is" in conditional formating, since those values are simply greater than, lesser than, or equal to 0 in all cases. ****EDIT**** I tried this, and it works... almost. Unfortunately, only greater than and less than 0 works. When I set a 3rd conditional value to format them bold, black when "equal to" 0, they appear blue when their value is 0. Anyone know what's up with that?

The automatic changing of background colors, however, is out of my range of Excel knowledge. I know I will need a formula, and this is what it needs to be able to do:

Our pricing is across the top of the chart in a row, each price/product in it's own column. Every row below that contains the pricing for each competitor on each product, i.e.:

My Company  |   $90    |   $80   |    $65   |   etc. 
X Company    |   $89    |   $80   |    $69   |   etc. 
Y Company    |   $92    |   $69   |    $69   |   etc. 
Z Company    |   $91    |   $78   |    $68   |   etc. 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So, if "My Company" is cell A1, then I need cells B2 through D4 (all the competitor's pricing) to be compared to ours (our pricing) by a conditional format formula to automatically change the background colors depending upon which prices are $1-$4 (green), $5-$9 (tan), or $10+ (yellow) less expensive than ours.

Unfortunately, I'm no programmer, or even all that good at math, so creating a formula to do this is beyond me.

Thanks in advance for any help... please let me know if I need to clarify this question any further.

I know that you can name a range to be able to use conditional formatting between worksheets and I know how to highlight changes in corresponding cells on the same worksheets using =NOT(A11=A2). However I can't seem to put the two together. Does anyone know the formula that I would use in conditional formatting to compare data ranges between worksheets?


i am trying to compare 2 columns that have summation formulas.

Unit Labor Labor Burden Total
Cost Rate (H) Time (M) Cost Cost
3 $1,920.23 177 $2,288.98
4 $0.00 10 $0.00
5 $0.00 10 $0.00
6 $0.00

i want to compare column (K) to column (O), highlighting (O) in green when a value has been entered in (K) - even if the value in (K) is $0.00.

ex. cell (O5) should be green cause (K5) has 0.00 entered as a null cost.
cell (O6) should be red cause (K6) is still blank.

what would be the conditional formatting for column (O)??


Hi All

Excel tells me I can't do conditional formatting using other worksheets but I think you guys may know different...

Briefly, I have a number of worksheets that list training info on several people and one which is the summary in the form of a matrix.

I've created a hyperlink to all the 'X''s in the summary sheet to go directly to the training qualification in the person info sheet so that you can see when the qual expires.

What I tried to do was conditionally format the 'X''s to be either green, amber or red based on the expiry date in each of the person info sheets.............BUT...............this is where Excel says 'NO'!!!

Any help would be much appreciated................Thanks

One way is to use INDIRECT with an incrementer, eg ROW(A1) ..

Assume you want the CF to trigger in col B in say, Sheet1, if corresponding
cells in col B in the source sheet: Mechelen-SMaarten-Upgrade 543 contain: "X"

In Sheet1,

Select col B (B1 active), then apply the cond format formula:
=INDIRECT("'Mechelen-SMaarten-Upgrade 543'!B"&ROW(A1))="X"
Format as desired. The CF will trigger as required. If B2, B20, B100 in the
source sheet contain: "X", then B2, B20, B100 in Sheet1 will be conditionally
formatted. Adapt to suit.
"Reinhart" wrote:
> Hey all,
> I want the format of a cell in worksheet 1 depending on a value of a cell in
> worksheet 2 without copying the value of this cell to worksheet 1. The excel
> functionality does not allow this. How can I work around it?
> Example:
> Formule Is: ='Mechelen-SMaarten-Upgrade 543'!$B$12 ="X"
> Example of format if condition is true: Lime color
> Thanks,
> Rein

Using MS 2007, I have a column that has 40 sets of three rows; the rows are
for three seperate years for 40 different schools that are included in the

The cells I am workign with show the performance indicator - a number - for
each of the three years for the school in that set.

I need to compare the three numbers for each set and show which set of three
shows gains, and eventaully determine the highest performer over the three

60 AVE 2008-09
61 AVE 2007-08
53 AVE 2006-07
69 BCE 2008-09
53 BCE 2007-08
56 BCE 2006-07
63 CPE 2008-09
64 CPE 2007-08
84 CPE 2006-07

How do I conditionally format the sets of numbers to show gain or loss
compared to the row beneath it?

Or a better suggestion for finding the highest performing school considering
the three years of performance?

I'm trying to change to fill color of a cell in 1 worksheet if the value of
another worksheet is greater than or equal to a certain value. I'm using
conditional formatting with FormulaIs and the normal If "worksheet cell" >

Help please.

Thanks in advance

Is there any way to do conditional formatting across two worksheets? So for
example if cell A1 on sheet 1 = 10 then cell A1 on sheet 2 is red.

Hi all,

Here is a challenge I am facing.

1. Two Columns w/ Integers - Column "A" and Column "B".
2. Conditional formatting testing if Values in Column "A" are less than Values in Column "B"
3. If the condition is true then a cell in the column "A" (Font = Bold, Text Color = WHITE, Cell Color = RED). Otherwise initial formatting is kept.

I did this via standard Excel functionality - Conditional formatting, and not via macros, because the Values in the Columns can change dynamically (updated by user), therefore I want the conditional formatting applied right away after the change is made.

So here is the problem:

In the beginning of every month I need to delete Column "B", Column "C" becomes Column "B". However, the conditional formatting "looses" the refernce area (which was column "B"), and gives "#REF!".

I would like to create a macro that would modify the rule after deleting Column "B", and change the "#REF!" to the "new" Column "B" (formerly Column "C").

I do it w/ the following code:

However, I would like the user to tell Excel via the InputBox Function which column to use as reference for comparing to
column "A". And then modify the conditional formatting accordingly (to reference to the user defined column).

When I put the following code:

Dim Msg As String
Dim R As Range

Msg = "Select a cell in the reference column"
Set R = Application.InputBox (Msg, Type:=8 )

Columns("A:A").FormatConditions(1).Modify xlCellValue, xlLess, R
The conditional formatting is done based on Values in the "R" Range, but not the the reference to the cells in "R" Range... Hence the conditional formatting is no longer dynamic because if Values in "R" Range are changed then you need to re-run the macro. But if I could make Excel take "R" as Range and not Values from "R" for the conditional formatting, it would have been perfect.


(Core starting issue solved but other threads were created to continue with trouble spots.)

I'm terrified to use VBA just yet, so right now I'm determined to highlight values in my spreadsheet just using conditional formatting.

I have 5 plus 1 columns of data. For example,

A B C D E....F
3 2 4 5 7....2
4 6 1 3 4....1
4 7 4 5 8....4
2 1 5 3 9....1

I successfully applied a MIN formula to compare between columns on each
row and display in F the MIN value. Notice each cell going down F has the
minimum value in analyzing across each row.


Now, I *also* want to apply a formula on the whole F column to compare these resultant MIN values
and give me the MINIMUM value between those as well, so now I'm comparing across
rows. Got it? Now then.....I want the ENTIRE row that has this MIN value in F to be highlighted, because that will be the best choice for the application of what I'm doing. Did you understand this paragraph. It might have been confusing, but I think I said everything correctly, so read it carefully.

I've tried all sorts of "Formula is", "Cell value is", dollar sign this, dollar sign that, no dollar sign this, <, >, =, highlighted ALL the block of data, highlighting JUST the column across the rows of which will be compared, only highlighted one cell, but nothing has worked.

Is my problem that you can't have 2 formulas in one cell? So then I thought I should ADD another condition, but nothing works.

Let me throw in one more element that I did not state. If anywhere down the F column there is a 0, I don't want that to be my minimum. I want it to evaluate and give me the minimum value greater than 0.

Please read everything I wrote carefully. It might be confusing, but I hope someone has an answer, because it sure has frustrated me.

Now, I'm thinking that VBA programming may give me answers, but I am so terrified of macros, I don't know how to begin that. I have this impression based on the many sites I've looked at on the internet that many people do macros and it's quite easy, but it's funny to me that I am so scared. There's something about me freaking out that I won't have a period or a quote in the right place and I'll stress my day away over it.

Anyway, thank you for giving thought to my problem. I'll be so happy!! when this is figured out.


Hi all,

I wonder if someone can help........

I am trying to apply conditional formatting to cells in a column. I want to highlight any cells which show a greater than 10% drop from the previous value, in the cell above.

For example: if D3 is less than 0.9 times the value of D2, then highlight the cell in red. Similarly highlight the cell when D4 is less than 0.9 times the value of D3, D5 is less than 0.9 times the value of D4, etc, etc. I have attached a section of my spreadsheet to demonstrate.

I can set the formatting for the first cell with {cell value<"0.9*$D$2"} and this highlights the cell as required.

However, when I apply the formatting to $D$3:$D$14 (in the conditional formatting rules manager) it compares each cell value to the value in D2, rather than to the cell above. So I tried deleting the dollar signs to give {cell value<"0.9*D2"}. This still compared all cells to D2. I tried using {cell value<"0.9*R[-1]C"}, but this returned an invalid formula error.

At wits end, any suggestions gratefully received.

I've had a search through the forum and can't quite find what I'm after, so apologies if this has been answered before and I simply can't find it!

In one Worksheet, I have a list of contacts, all of which have a unique (alphanumeric) reference codes in column A.

In another worksheet, I have a list of these reference codes for contacts interested in a product (imported from another source).

I'm trying to get Excel to conditionally format the rows in the first Worksheet (where the contact details are) depending on whether the reference number exists in the exists in the imported data in the second worksheet. The second worksheet also has the reference codes in column A.

I can't get the above to work at all...

Here's the kicker though...I need it to work in a template spreadsheet I have. So, I have a spreadsheet empty of data, import the contacts into one worksheet, then eventually import the interested contacts into the other worksheet, thus the conditional formatting needs to work on the entire column ($A:$A) and not a specific range. The number of contacts and interested contacts can vary greatly...anything from a couple of hundred to a good few thousand.

Any help much appreciated.

Oh, and I'm using Excel 2004 (Mac)...

Good morning,

I have spent some considerable time creating a shift planner, which contains 40+ sheets based on months.

The shift pattern I have used has now changed! This has resulted in formula and conditional formatting issues on every sheet. Instead of having to go through every sheet I wondered if someone could help with some code.

What I need is for each sheet G9 - AK9 to change teh formula from:

=SUMPRODUCT(--($A$16:$A$51="NBO")+($A$16:$A$51="NPO"),--(G16:G51="2pm - 11pm")+(G16:G51="3pm - 1am"))

To: =SUMPRODUCT(--($A$16:$A$51="NBO")+($A$16:$A$51="NPO"),--(G16:G51="2pm - 11pm")+(G16:G51="3pm - 1am")+(G16:G51="3pm - 11pm"))

The conditional formatting then needs to change within several ranges: G24 - AK31 & G33 - AK51. Rule two is currently:

=OR(G17="2pm - 11pm",G17="3pm - 1am")

Which needs to change to:

=OR(G17="2pm - 11pm",G17="3pm - 1am",G17="3pm - 11pm")

I need to then replicate this on every sheet in the workbook! Nightmare! I've only been able to include 1 month due to the restrictions on uploads.

Any help would be much appreciated. Example worksheet attached.ShiftIssue_CF_Formula.xls

Can I assign a range to a formula when setting a conditional format?

'compare values in column B to adjacent values in column C
'(lr = last row)
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions.Add _
Type:=xlCellValue, Operator:=xlGreater, Formula1:="C2:C" & lr
'apply formatting
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions(1).Font.ColorIndex = 11

This code results in:
Error Number 5: Invalid procedure call or argument

The problem, I think, is with:
Formula1:="C2:C" & lr

How do I assign conditional formatting to each cell in column B?

Thanks in advance.

I'm trying to change to fill color of a cell in 1 worksheet if the value of
another worksheet is greater than or equal to a certain value. I'm using
conditional formatting with FormulaIs and the normal If "worksheet cell" >

Help please.

Thanks in advance

I am new here so I hope my discription in the title is OK.

I am trying to do a conditional format of 0 to 2 is red, 3 to 4 is yellow and 5 is green. However I also need to have a cell that is blank to remain white and this is the part I am having problems with.

I found the following code since I cannot use the standard conditional formating in excel since it needs 4 conditions. If you can help or lead me in the right direction I would greatly appreciate it.


Tony Mireles

    Dim icolor As Integer 
    If Not Intersect(Target, Range("H3:H12")) Is Nothing Then 
        Select Case Target 
        Case 0 To 2 
            icolor = 3 
        Case 3 To 4 
            icolor = 6 
        Case 5 
            icolor = 4 
        Case Else 
            icolor = 2 ' white
        End Select 
        Target.Interior.ColorIndex = icolor 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi guys
Could anyone explain how to have conditional formatting compare the value of say - E6 and assign the same value to E7? (Office 2003)
Tried this but getting no response:-

Condition 1
Formula is: if(E7=2,E6=2,1)


I was looking to use the VBA conditional formatting script posted on OzGrid and was curious if it would be easy to make some slight modifications.

    Dim icolor As Integer 
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 
        Select Case Target 
        Case 1 To 5 
            icolor = 6 
        Case 6 To 10 
            icolor = 12 
        Case 11 To 15 
            icolor = 7 
        Case 16 To 20 
            icolor = 53 
        Case 21 To 25 
            icolor = 15 
        Case 26 To 30 
            icolor = 42 
        Case Else 
        End Select 
        Target.Interior.ColorIndex = icolor 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I can see that Case is the number range referenced to change the background...However, would it be possible to have it read a cells value as the criteria for the color change?

Currently, I would like it to reference values in range I2:M2 and anything matching those cells in range A4:E28 change background to icolor = 30

I'm just not sure what to replace Case with to make it refence cells I2:M2


Help! I need to create conditional formatting that highlights a cell, if the
value in column F is greater than the value in column B. How do I make that
work? Thanks!

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