+ Reply to Thread
Results 1 to 6 of 6

Automating conditional formatting based on > or < cell reference

  1. #1
    Registered User
    Join Date
    08-28-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2010
    Posts
    11

    Automating conditional formatting based on > or < cell reference

    Hey everyone,

    I'm working on conditionally formatting 200+ cells to show up as either red or green based on if the numbers in them are greater or less than they were in the previous report. Its very tedious to have to create two separate rules to do this. Can anyone think of a way to do a macro or something to automate it?

    The two charts are exactly the same size so the two cells that are being compared are always the same distance from each other if that helps. Looks like this basically:

    If $K$1>$A$1 then color green
    If $K$1<$A$1 then color red

    next

    If $K$2>$A$2 then color green
    If $K$2<$A$2 then color red

    Their are like 10 tables that are in 5x5 matrices

    thanks in advance!

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

    Re: Automating conditional formatting based on > or < cell reference

    You can apply the conditional formatting to all the cells in one operation, by highlighting the cells first and then using relative cell references instead of absolute. For example, suppose you want this to apply to the cells in the block A1:E5, then select all those cells with A1 as the active cell, and then you can use these formulae in the CF conditions:

    =K1>A1 --> green
    =K1<A1 --> red

    When you exit the CF dialogue box Excel will automatically adjust the cell references to suit.

    If you set it up this way you can also use the Format painter to effectively copy the format (and conditional format) to other cells, eg A11:E15.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    08-28-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Automating conditional formatting based on > or < cell reference

    Thanks! Removing the $ signs from the cell ranges when making the new conditions did the trick!

  4. #4
    Registered User
    Join Date
    10-12-2012
    Location
    yorkshire
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Automating conditional formatting based on > or < cell reference

    Hi I have a spreadsheet where I have used conditional formatting which works fine, however I am wanting the rule to run on a macro so have recorded a macro to highlight sales greater than 200 and sales lower than 2000 which turns the row red and green, the conditonal formatting worked fine but this did not run on the macro. I also recorded a further macro to remove all conditional formatting on the spreadsheet and then expected to click the button for high sales and this to work but nada.

    Any suggestions folks.

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

    Re: Automating conditional formatting based on > or < cell reference

    You should start your own thread rather than tag on to someone else's - take a look at the Forum Rules at the top of the page.

    Pete

  6. #6
    Registered User
    Join Date
    10-12-2012
    Location
    yorkshire
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Automating conditional formatting based on > or < cell reference

    ok will do thanks.

+ 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