Formula based on formatting?

[samilynn]

Can I put a formula in, say, cell D1, that says: "if A1, B1, and C1 are all with yellow cell background, then D1 says "yellow"?

Thank you

Samantha

 

[Joe4]

I am pretty sure that native Excel formulas can only run on values in cells, and not their formatting.

However, how are these cells colored?
If they are colored by Conditional Formatting, we may be able to use the same rules/criteria that we used in Conditional Formatting in our formulas.

If they are being colored manually, I think that would require a VBA solution.

 

[samilynn]

Hi Joe, thanks for the reply. The cells are colored manually. I wish I could use Conditional Formatting, but they have to be done manually (as they are reviewed based on the data in them)

 

[Joe4]

Are you interested in a VBA solution?

If so, please provide specific detail, as it will determine how we write the VBA code your original question seems like it may be an over-simplified example, and it is important for us to fully understand the full scope of this, and how it will be used).

Some important questions include:
1. How many cells might you be checking at one time?
2. Should it always say "yellow", or do you want it to somehow dynamically identify the color being used and return that (I am not sure if that is even possible, as there are many different shades of colors)?

 

[samilynn]

Thank you, Joe!
Here are the specific details: if cells F1, G1 and K1 are all the color yellow (which I believe is #FFFF00 in the color palette), then enter something (really, anything: "yellow", or "done", or "joe" , in cell M1
And the same going down to other rows, obviously.

Thanks again

Samantha

 

[Joe4]

OK, the potential issue that I see is that just changing the formatting (i.e. color of a cell) does not trigger a new calculation in Excel. So I don't think we can make any formula, even in VBA, that will "update" automatically as you change the color of cells (without updating their actual values). I think the best you can do is to make a macro that runs whenever the cursor is moved (i.e. a different cell is selected).

However, we can write a script that will run manually on the existing data in the sheet. So this will work, it will just need to be run manually anytime you make updates to cell colors.

Sub CheckColors()

    Dim lr As Long
    Dim r As Long
    Dim ct As Integer
    Dim c As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column F with data
    lr = Cells(Rows.Count, "F").End(xlUp).Row
   
'   Loop through all rows
    For r = 1 To lr
'       See if columns F, G, and K are all yellow
        If (Cells(r, "F").Interior.Color = 65535) And _
            (Cells(r, "G").Interior.Color = 65535) And _
            (Cells(r, "K").Interior.Color = 65535) Then
'           Update column M
            Cells(r, "M") = "yellow"
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

Hopefully that works for you.

If you want that code to run automatically anytime you move the cursor in Excel, you can call this code from a Worksheet_SelectionChange event procedure, i.e.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    CheckColors
End Sub

This code MUST be placed in the sheet module that you wish to run this against.

Just note that if you change the color of a cell, but do not move off of the cell, the code will not be triggered to run.

 

[samilynn]

Joe, thank you very much, your VBA code works perfectly!!

Much appreciated,
Samantha

 

[Joe4]

You are welcome!