Conditional Formatting
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Conditional Formatting

  1. #1
    Guest

    Default

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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 .

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,872
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com