+ Reply to Thread
Results 1 to 4 of 4

Conditional formatting based on a formula on another sheet

  1. #1
    Registered User
    Join Date
    12-22-2019
    Location
    British Columbia
    MS-Off Ver
    Office 2019
    Posts
    8

    Conditional formatting based on a formula on another sheet

    Greetings..... I would like to format a three columns depending on the result of a formula on another sheet within the same workbook.

    Column A | Column B | Column C The result of the formula is one of 3 results (1 or 2 or 3)
    If result is 1 - Green | Grey | Grey
    If result is 2 - Gray | Green | Grey
    If result is 3 - Grey | Grey | Green

    Please let me know if you require more information!

    Thanks

    Steve

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,831

    Re: Conditional formatting based on a formula on another sheet

    Ok Steve.

    Break this down.
    A: Conditional formatting overrides your fixed format
    B: 6 of your 9 Cells are Grey to Set the fixed format for Columns A to C to Grey

    Set Conditional formatting for the Range =$A:$A using the formula =Sheet1!$A$1=1 to Set the fill colour to Green

    Set Conditional formatting for the Range =$B:$B using the formula =Sheet1!$A$1=2 to Set the fill colour to Green

    Set Conditional formatting for the Range =$C:$C using the formula =Sheet1!$A$1=3 to Set the fill colour to Green
    Attached Files Attached Files
    Last edited by mehmetcik; 02-28-2020 at 04:12 PM.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Registered User
    Join Date
    12-22-2019
    Location
    British Columbia
    MS-Off Ver
    Office 2019
    Posts
    8

    Re: Conditional formatting based on a formula on another sheet

    So if the result is Sheet"References"($I$5)formula produces the result of 1, I would like Column A fill Green, and if the same cell has a result of 2 or 3, I would like it to fill Grey.

    If the result is Sheet"References"($I$5)formula produces the result of 2, I would like Column B fill Green, and if the same cell has a result of 1 or 3, I would like it to fill Grey.

    and if the result is Sheet"References"($I$5)formula produces the result of 3, I would like Column C fill Green, and if the same cell has a result of 1 or 2, I would like it to fill Grey.

    I would think this wouldn't be so difficult, but when I try to write the condition for even just the cell, it will not work

    eg: ="References"$I$5=1 - Fill Green (and it does nothing!)

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,873

    Re: Conditional formatting based on a formula on another sheet

    Assuming the entries in References!I5 are limited to 1, 2 or 3 then:
    1. Formula to turn column A green: =References!$I$5=1
    2. Formula to turn column A grey: =References!$I$5<>1
    Similar formulas are used for columns B:C
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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 on a second sheet, based on two cells
    By Belinda Vetinari in forum Excel General
    Replies: 3
    Last Post: 11-02-2018, 09:30 AM
  2. [SOLVED] Conditional Formatting based on matching data from another sheet
    By Crawfy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2018, 03:18 AM
  3. Conditional Formatting based on another sheet's cell
    By saim1611 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-18-2016, 04:03 AM
  4. [SOLVED] Conditional formatting based on range of cells in different sheet
    By iveta96 in forum Excel Formulas & Functions
    Replies: 41
    Last Post: 06-01-2015, 10:45 AM
  5. Replies: 5
    Last Post: 01-09-2014, 02:13 PM
  6. Conditional formatting based on value in diff sheet
    By shikamikamoomoo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-29-2010, 12:02 AM
  7. Conditional formatting based on another sheet
    By goofy78270 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-22-2007, 04:19 PM

Tags for this Thread

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