if statement based on background color in range of cells

  • Hi Guys.
    I am using the following function:

    Code
    Function CellColour(Irow As Integer, Icol As Integer) As Long
    CellColour = Cells(Irow, Icol).Interior.colorIndex
    End Function


    and the following if statement:
    =IF(CellColour(20,18)=35,"Red","Not Red")


    This works well for one cell but I need a function that will cover the range T18:T38. The if statement will be ' if any cell in that range has the background color red(35),True, False.


    Thanks in advance.

  • Re: if statement based on background color in range of cells


    If you can change the call to the procedure to include a range rather than a Row, Column and (for example) a Colour Code, then you can turn it into a general procedure that determines if any cell in any range has a particular background colour:


  • Re: if statement based on background color in range of cells


    Thanks cytop.
    Can you give an example of how the statement would look e.g.
    =IF(CellColour(T18:T38)=35,"Red","Not Red")

  • Re: if statement based on background color in range of cells


    Can you please check again KrishnaKumar. When I put on zeros in that range, which changes the cell colour from red, it comes back as true instead of false.

  • Re: if statement based on background color in range of cells


    You never mentioned the cells were conditionally formatted...


    CF cells report the underlying backcolour, not the colour applied by the conditional formatting. This is a completely different problem. See here for code examples to determine the colours applied by Conditional Formatting

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!