I need to compare data from 2 worksheets. There are three rows of data I

need to compare in each worksheet:

WS - A

A,B,C,D,E,F,

WS - B

A,B,C,D,E,F

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.....

need to compare in each worksheet:

WS - A

A,B,C,D,E,F,

WS - B

A,B,C,D,E,F

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.....

- Conditional Formatting - comparing worksheets
- Conditional Formating --- Comparing Value in 2 fields
- Conditional Format compare first 5 digits across worksheets
- Conditional Formatting - 2 Worksheets one Named Range
- Conditional Formatting Across Worksheets
- Conditional format compare values columns/rows
- Apply Conditional Formatting Across Sheets
- Conditional Formatting - Comparing two cells
- Conditional formatting between worksheets problem
- Conditional formatting between worksheets
- Conditional Formatting: comparing cell values
- Conditional formatting between worksheets?
- Conditional formating across worksheets
- Conditional Formatting comparing 2 clmns - one is deleted, setting new refernce clmn
- Conditional Format Comparing Data Between Rows
- Conditional Formatting - comparing value to previous cell in column
- Conditional Formatting Across Worksheets
- Replicate formula change and conditional formatting throughout worksheet
- How to set conditional format with vba?
- Conditional formatting between worksheets?
- Conditional Formatting: VBA & Worksheet Change Event
- Conditional formatting match value of adjacent cell
- Conditional Formatting: Compare Range To Another Range
- Conditional formatting comparing two columns

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"?

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 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).

Bernard

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.

Thanks

Sam

--

Message posted via OfficeKB.com

http://www.officekb.com/Uwe/Forums.a...tions/200701/1

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.:

VB: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.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

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 am trying to compare 2 columns that have summation formulas.

K L M N O

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)??

thanks...

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

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.

--

Max

Singapore

http://savefile.com/projects/236895

xdemechanik

---

"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

for three seperate years for 40 different schools that are included in the

chart.

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

years.

Sample:

# SCHOOL YEAR

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?

THANKS!

Synthia

another worksheet is greater than or equal to a certain value. I'm using

conditional formatting with FormulaIs and the normal If "worksheet cell" >

whatever

Help please.

Thanks in advance

example if cell A1 on sheet 1 = 10 then cell A1 on sheet 2 is red.

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).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.

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

(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.

Great.

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.

-D

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.

Thanks

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)...

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

'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.

another worksheet is greater than or equal to a certain value. I'm using

conditional formatting with FormulaIs and the normal If "worksheet cell" >

whatever

Help please.

Thanks in advance

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.

Thanks

Tony Mireles

VB:Range) 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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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.

VB: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?Range) 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 'Whatever End Select Target.Interior.ColorIndex = icolor End If End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

Thanks!

value in column F is greater than the value in column B. How do I make that

work? Thanks!

--

Shelina

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