How to set cell color based on another cell color

Question

Sorry, I am new at MS Excel.

I want to set cell A1 color to red if cell G1 is green

and the same time

I want to set cell A1 color to yellow if cell F1 is not empty

If two rules are true, priority should have the second one. If no rule is true, nothing should be done with cell.

How to do that?

Answer 1

Are you familiar with VBA? You can use the code below. If you are not familiar with VBA, use the conditional formatting option.

Conditional formating:

1. Mark Cell A1.
2. Go To conditional Formatting -> New Rule
3. Go to use a formula to determine which cells to format
4. Insert your formula (e.g. "=F1="") (this will check f F1 has no value)
5. Select your desired format (e.g. color etc.) below
6. Confirm your selecttion with "OK"

VBA: You can activate cells with

 Range("A1")

Each cell has several parameters like colors or values, that you can read and edit.

 Range("A1").Value 'Value
 Range("A1").Interior.ColorIndex 'Color of cells

Code for your requirements could look like

 If Range("G1").Interior.ColorIndex = 4 then  '4 = color code for green
    Range("A1").Interior.ColorIndex = 3       '3 = color code for red
 End If

 If (Range("F1").Value = 0 Then
    Range("A1").Interior.Colorindex = 6  '6 = color code for yellow
 End If

You can find the color-codes here: Microsoft Docs Color Codes

Answer 2

You'll need to use VBA code to trigger that first rule, because conditional formatting won't pick up on other cells' colours. Newbie guide:

1. Type Alt+F11 to open the VBA code editor window (looks scary for a newbie I know, been there!)
2. In the window on the left you need to select your worksheet - It will be in a window titled 'Project', and you might have to expand 'VBAProject(filename)' and then 'Microsoft Excel Objects'. Look for the sheet name.
3. At the top there should now be 2 dropdown boxes, saying 'general' and 'declarations'. You need to change 'general' to 'Worksheet' and 'declarations' to 'SelectionChange' (might do that automatically) and then some code will appear - the two lines showing the start and end of a 'sub'. Any code you paste between those lines will run every time a new cell is selected in the worksheet (but not any other worksheet).
4. In between those lines paste this code:

If ThisWorksheet.Range("F1").Value <> "" Then
    ThisWorksheet.Range("A1").Interior.Color = vbYellow
ElseIf ThisWorksheet.Range("G1").Interior.Colour = vbGreen Then
    ThisWorksheet.Range("A1").Interior.Colour = vbRed
End If

5. Close the code editor and test it a bit!

Bear in mind that it's checking if G1 is vbGreen - Excel is very rigid and this won't trigger on just any shade of green. Test it and see if it works.