+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting, with formula based on the value from another cell

  1. #1
    Registered User
    Join Date
    01-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    96

    Conditional Formatting, with formula based on the value from another cell

    I want to use conditional formatting to change the cell color in column B for the cell value in column H. I will need multiple rules because there are multiple colors. Can this be done? See attached. My whole sheet has about 100 rows in various columns.
    Attached Files Attached Files
    Last edited by Alphabex; 08-26-2015 at 07:43 PM.

  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, with formula based on the value from another cell

    Select the cells from B13 to B21 (or however far your names extend to in your real file), then click on Conditional Formatting | New Rule | Use a Formula... from the bottom of the list of options, then put this formula in the dialogue box:

    =$H13="S"

    Click on the Format button | Fill tab and choose your colour (light blue in this case), then click OK twice the exit the dialogue box.

    Then you need to repeat this 6 more times, changing your code and colour each time. Excel will automatically adjust the cell references to suit the range initially selected.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    01-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    96

    Re: Conditional Formatting, with formula based on the value from another cell

    Do I need to do this for each cell I want to highlight? Can the first part of the formula "$H13" be $H13:$H100 and still work?

  4. #4
    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, with formula based on the value from another cell

    No and No - you select (highlight) all the cells first and then apply the CF seven times only with the formula referencing the first cell in the selected range (i.e. H13 in your example, as your names start from B13), then Excel will adjust the references automatically to all the cells that you had selected.

    Hope this helps.

    Pete

    EDIT: An alternative way is to apply the CF conditions to only one cell (B13), then you can use the Format Painter to effectively copy those conditions to the other cells in the range

  5. #5
    Registered User
    Join Date
    01-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    96

    Re: Conditional Formatting, with formula based on the value from another cell

    Thank you! Solved!

+ 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. [SOLVED] Conditional formatting based on result of formula within the same cell
    By som3on3_10 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2014, 09:10 PM
  2. [SOLVED] Formula based off cell colors / conditional formatting?
    By dsciola in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-30-2013, 03:48 AM
  3. Formula for conditional formatting based on text in another cell
    By dbwoods11 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-15-2013, 05:25 AM
  4. Replies: 3
    Last Post: 05-25-2012, 03:30 PM
  5. conditional formatting based on formula not cell value
    By Ramzes in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-03-2011, 07:34 AM
  6. Replies: 1
    Last Post: 05-20-2010, 02:07 PM
  7. Replies: 2
    Last Post: 08-03-2006, 05:45 AM

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