Conditional Formatting

[Guest]

I keep a workbook with two worksheets in it. The first contains a list of all of our, and our competitor's, products, capacities, etc. by location. This information is based on widely published (Annual reports, websites, etc.) data.

The second is nearly a duplicate of the first, but adjustments are made based on any additional (less reliable?) sources (trade conference conversations, newspaper articles, etc.).

Initially, I set the second worksheet to just read the values of the first. Then when I started adding modified entries to the second worksheet, I manually formatted the second page entries to make them standout.

Is there an easy way to use the Conditional Format capability across different worksheets (i.e. if Sheet1!A1 <> Sheet2!A1, format red)?

 

[Mudface]

As far as I know you cannot reference across worksheets or workbooks using conditional formatting. It should be possible to do this via a macro, I'm sure someone'll oblige .

 

[Mudface]

Actually, the following seems to work OK: -

Public Sub CompareSheets()

Dim Sheet1Range As Range
Dim Sheet2Range As Range
Dim c As Range

Set Sheet1Range = Sheet1.UsedRange
Set Sheet2Range = Sheet2.UsedRange
Application.ScreenUpdating = False

For Each c In Sheet2Range
If c.Value<> Sheet1.Range(c.Address) Then
c.Font.ColorIndex = 3
End If
Next c

Application.ScreenUpdating = True

End Sub
This message was edited by Mudface on 2002-03-13 07:28

 

[Aladin Akyurek]

On 2002-03-13 07:12, Anonymous wrote:
I keep a workbook with two worksheets in it. The first contains a list of all of our, and our competitor's, products, capacities, etc. by location. This information is based on widely published (Annual reports, websites, etc.) data.

The second is nearly a duplicate of the first, but adjustments are made based on any additional (less reliable?) sources (trade conference conversations, newspaper articles, etc.).

Initially, I set the second worksheet to just read the values of the first. Then when I started adding modified entries to the second worksheet, I manually formatted the second page entries to make them standout.

Is there an easy way to use the Conditional Format capability across different worksheets (i.e. if Sheet1!A1 <> Sheet2!A1, format red)?

Select the relevant cells in column A of Sheet1, go the Name Box on the Formula Bar, type DATA, and enter.

Select the relevant A-cells in Sheet2.

Activate Format|Conditional Formatting.

Choose 'Formula is' for Condition 1.

Enter in the formula box:

=ISNA(MATCH(A2,DATA,0))

where I assume that the data start in A2 in Sheet2 (otherwise adjust to suit).

Activate Format.

Choose red on the Patterns tab.

Click OK, OK.