Forum Discussion

WassimN's avatar
WassimN
Copper Contributor
Jan 24, 2023

Conditional Formatting based on cell color

Hello,

What I need help with is the following:

Say in cell A1 I have a value of 10. And in cell B1 I have a formula something like =IF(A1=10,"OK","").

In cell B1 I have some conditional formatting something like: If cell value = OK make the cell interior color green.

 

What I need is if I change the interior color of A1, the conditional formatting in B1 will change to No fill color, or to change the font color, or whatever.

 

So in summary I am looking for conditional formatting based on another cell's interior color.

 

I am using the latest and greatest Office 365 version.

 

Thanks for any ideas.

excel
Formulas and Functions
office 365

3 Replies

    • WassimN's avatar
      WassimN
      Copper Contributor
      Jan 25, 2023

      NikolinoDE 

      Thanks, but this is not quite exactly what I was hoping for.

       

      What I am looking for is:

       

      When a cell's interior color changes, then the Conditional Formatting will change.

      Example:

      Cell A1 interior color is green Conditional Formatting font color becomes Black

      Cell A1 interior color is blue Conditional Formatting font color becomes yellow

      Cell A1 interior color is Purple Conditional Formatting font color becomes red.

       

      Any time I change A1s interior color, the CF Font changes accordingly.

       

      My research so far shows that there are no conditional formatting to check cell interior color, so I am thinking that a VBA code snipit is going to be the solution, but since I know there are many creative Excel experts here, I wanted to ask first just in case...

      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        Jan 25, 2023

        WassimN 

        this macro would be an approach, but without a value in a cell I don't think this can be done. Excel needs values or text to do anything, but I don't know of any way that can be automated with colors.

         

        Option Explicit
Sub Colour()
Dim c As Range
For Each c In Range("A1:D15, A1:D15")
On Error Resume Next
c.Font.ColorIndex = c.Interior.ColorIndex = 2
Next
End Sub

         

         Thank you for your patience and understanding

Resources