none
VBA (or Formula or Conditional Formatting) to Colour a Cell Based on Conditional Formula RRS feed

  • Question

  • I have a number of situations where I manually colour cells as an alert, based on the data in adjoining cells. I'd like to automate this process and have been trying to do it using conditional formatting, totally without success. Help in achieving my aim would be greatly appreciated.

    As one example, col V is an "alert" column. Each cell in col V has a formula in the form of:
    =IF(AND(ISTEXT(D291),ISTEXT(F291),ISTEXT(J291),ISTEXT(L291)),"X","")

    I would like to replace "X" with the equivalent of:  colours cell V291 with red fill.

    I'm happy to use either VBA, or a formula, or conditional formatting - whichever is the most sensible way to do it.

    This is the simplest of my uses as col V has a plain background (= no fill). I have other situations where the cell already has a fill colour. I don't want to complicate the first query, so maybe I'll wait until I can achieve the simple use before introducing a more complex setting.
    Monday, January 22, 2018 12:24 PM

Answers

    • Select the range in column V that you want to color conditionally. I'll assume that V2 is the active cell within the selection.
    • On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    • Select 'Use a formula to determine which cells to format'.
    • Enter the formula


    =AND(ISTEXT(D2),ISTEXT(F2),ISTEXT(J2),ISTEXT(L2))

    • Click Format...
    • Activate the Fill tab.
    • Select red.
    • Click OK twice.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    • Edited by Hans Vogelaar MVPMVP Monday, January 22, 2018 2:39 PM
    • Proposed as answer by Ashidacchi Monday, January 22, 2018 11:19 PM
    • Marked as answer by DaviDWF2 Tuesday, January 23, 2018 1:38 PM
    Monday, January 22, 2018 2:39 PM

All replies

    • Select the range in column V that you want to color conditionally. I'll assume that V2 is the active cell within the selection.
    • On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    • Select 'Use a formula to determine which cells to format'.
    • Enter the formula


    =AND(ISTEXT(D2),ISTEXT(F2),ISTEXT(J2),ISTEXT(L2))

    • Click Format...
    • Activate the Fill tab.
    • Select red.
    • Click OK twice.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    • Edited by Hans Vogelaar MVPMVP Monday, January 22, 2018 2:39 PM
    • Proposed as answer by Ashidacchi Monday, January 22, 2018 11:19 PM
    • Marked as answer by DaviDWF2 Tuesday, January 23, 2018 1:38 PM
    Monday, January 22, 2018 2:39 PM
  • Thanks Hans - works perfectly.

    I think where I was going astray previously was in not matching my formula with the active cell, and then having cells highlighted that shouldn't be, and cells not highlighted when they should be. But now - all resolved.

    I'll apply that little lesson to my other situations and will hopefully be able to successfully colour fill them, otherwise I'll come back here. If all is well in 2 - 3 days, I'll mark this as answered.

    Thanks again Hans - much appreciated.
    Monday, January 22, 2018 3:24 PM