+ Reply to Thread
Results 1 to 5 of 5

Change text colour based on list option chosen

  1. #1
    Registered User
    Join Date
    05-20-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Change text colour based on list option chosen

    Hi everyone,

    I am working on a spreadsheet for financial data which is based on forecast and actual data. As it's the start of our financial year we don't have any actual data yet so all figures in all the 'actual' rows match the dummy forecast data.

    Dummy sheet.xls

    What I'm wondering is if there's a way to do the following:

    If I choose 'actual' from the list at C3, D3, etc instead of 'forecast', is there a way to set it up so that it changes the text colour (or fill colour) of the corresponding actual data at C9, C15, C21, C27 and C33? ie. so that it's clear by colour when data is 'actual' and when it is 'forecast'?

    FYI I'm using Excel 2007 but a lot of others use 2003 so it needs to be as compatible as possible.

    Any help is greatly appreciated!

  2. #2
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Change text colour based on list option chosen

    Follow these steps:

    Select C9.
    On the Home Tab click Conditional Formatting and choose New Rule.
    Select 'Use a formula to determine..' and for the formula below type in =C$3="Actual"
    Click the Format button and apply whatever you want to do to the cell (colour change etc.) and click OK to confirm your changes.

    Check this is working and if it is:
    With C9 selected click the Format Painter button. Then select all the cells to the right of this and you have set up that row to change colour.

    Repeat this painter step for each row. If you are having trouble with anything let us know.
    Last edited by Harribone; 07-11-2013 at 06:54 PM. Reason: Put $ in correct place
    Say thanks, click *

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Change text colour based on list option chosen

    Duplicate idea
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Registered User
    Join Date
    05-20-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Change text colour based on list option chosen

    Quote Originally Posted by Harribone View Post
    Follow these steps:

    Select C9.
    On the Home Tab click Conditional Formatting and choose New Rule.
    Select 'Use a formula to determine..' and for the formula below type in =C$3="Actual"
    Click the Format button and apply whatever you want to do to the cell (colour change etc.) and click OK to confirm your changes.

    Check this is working and if it is:
    With C9 selected click the Format Painter button. Then select all the cells to the right of this and you have set up that row to change colour.

    Repeat this painter step for each row. If you are having trouble with anything let us know.
    Thanks so much, Harribone! Your instructions were great and really easy to follow. However, I don't want the whole row to colour when I choose Actual at C9. Essentially each month we will change that month from Forecast to Actual.

    Is the only way to format the colour change to do each individual cell of each month and each row separately? I can do that (of course) but is there a more efficient way so that when I choose 'Actual' for July, it changes the format for ALL the cells in that column labelled expense actual, revenue actual, etc (ie. C9, C15, C21, C27, C33)?

  5. #5
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Change text colour based on list option chosen

    When you copy this across the row it will apply the formatting rule but each one will only change when the top cell is set to actual (the formula changes from =C$3 to =D$3, =E$3 automatically).

    You could use the format painter and select the whole range to applay formatting to all the cells in one go for quickness but it you do that gap rows you have will end up changing colour too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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