+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting Across Sheets

  1. #1
    Registered User
    Join Date
    05-02-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    20

    Conditional Formatting Across Sheets

    Hello Everyone!

    How to make conditional formatting through sheets.

    Sheet 1:
    a1 = name b1 = surname
    a2 = name b2 = surname
    a3 = name b3 = surname
    etc ...

    Sheet 2:

    a1 = 1
    b1 = -5
    c1 = 4
    etc ...

    If the number a1 on sheet 2 is positive, then a1 is on sheet 1 green. If the number a1 on sheet 2 is negative, then a1 is on list 1 red, etc ...

    The check range is from a1: c1 to sheet 2.

    Best regards

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Conditional Formatting Across Sheets

    You can't directly refer to another sheet in a Conditional Formatting formula, but you can give a name to the numbers in column A of Sheet2 (using Name Manger) and then use that name within your formula.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    05-02-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    20

    Re: Conditional Formatting Across Sheets

    https://www.excelforum.com/attachmen...1&d=1506204789
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-02-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    20

    Re: Conditional Formatting Across Sheets

    a1 = 1 = first
    b1 = -5 = second
    c1 = 4 = third

    I set two options on the first sheet:

    formulas:

    = first > 0 (green color)
    = first < 0 (red color)

    So I did the same for other names and so I got what I wanted.

    Each possibility has 2 conditional formatting. If I have 10 numbers then I have 20 conditional formatting. Can this be simplified or must this be the case?
    Last edited by carduel; 09-23-2017 at 06:59 PM.

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Conditional Formatting Across Sheets

    You have five rows on sheet1; but, four columns on sheet2??? Shouldn't the be the same size?

    Neverthe less use:
    Please Login or Register  to view this content.
    Where, CheckRange is the named range A1:D1 on sheet2
    Attached Files Attached Files
    Last edited by protonLeah; 09-23-2017 at 07:04 PM.
    Ben Van Johnson

  6. #6
    Registered User
    Join Date
    05-02-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    20

    Re: Conditional Formatting Across Sheets

    You are right, by mistake I wrote 4, sheet 2 should be 5 columns.

    If sheet 1 has 10 the same must have sheet 2.

  7. #7
    Registered User
    Join Date
    05-02-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    20

    Re: Conditional Formatting Across Sheets

    Thanks ProtonLeah,

    works perfectly!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional formatting between sheets
    By gz3s36 in forum Excel General
    Replies: 0
    Last Post: 08-30-2017, 11:00 AM
  2. [SOLVED] Conditional Formatting between sheets
    By jimboryan in forum Excel General
    Replies: 6
    Last Post: 07-31-2017, 09:01 PM
  3. Conditional Formatting Across Sheets
    By miamihurricanes in forum Excel General
    Replies: 0
    Last Post: 05-12-2011, 04:40 PM
  4. Conditional Formatting between sheets
    By Shazz in forum Excel General
    Replies: 3
    Last Post: 04-13-2011, 08:31 AM
  5. conditional formatting between sheets
    By gfstearns in forum Excel General
    Replies: 1
    Last Post: 03-24-2011, 04:33 PM
  6. Conditional formatting between 2 sheets
    By KINNEY0201 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-16-2010, 09:39 PM
  7. Conditional formatting between sheets
    By scottrobby in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-07-2008, 07:32 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1