My template involves three cells, but only one cell needs help (the 3rd).

The first cell will equal either $0 or something greater. The second cell will be greater than $0 or be blank. The third cell should equal the value in the second cell only if the first cell is not equal to $0. If the value of the first cell is equal to $0, then the third cell should equal $0.

The first cell is a simple formula based on other non-related inputs from the user. The font is always red.

The second cell is a simple user input. The font is always blue here, but the user may manually fill the cell with yellow color (based on whether the value is actual or estimated).

The third cell should be in bold green font if it’s value is greater than $0, and red font (not bold) if it’s value is equal to $0. Also, this cell should be filled with yellow color only if the second cell is filled with yellow AND if the first cell value is greater than $0.

I’m thinking the solution involves a combination of these functions; which is beyond my EXCEL skill level. 1) IF/THEN formula 2) Conditional Formatting 3) VBA code

What should I do to the third cell in order to get all that I want automatically (without the user needing to touch the third cell)?

Thanks in advance!

  • 1
    If the second field is arbitrarily filled, not with Conditional Formatting, then the only method is vba. Even then it will require the vba to be run manually, I do not believe there is a trigger event based on the format of a cell being changed. – Scott Craner Apr 26 '18 at 17:40

Here's what I came up with. Not sure how familiar you are with VBA, but if you'd like I can explain this code and how you'd go about implementing it in more detail.

I added two buttons to the sheet, one which formats the color/font of the cell, and another which clears the color/font.

enter image description here

Sub FormatColor3()

Set myWS = ThisWorkbook.Sheets("MySheet")
Set cell3 = myWS.Range("C2")

' Determining what color font is needed

If cell3.Value > 0 Then

    cell3.Font.Color = vbGreen
    cell3.Font.Bold = True

ElseIf cell3.Value = 0 Then

    cell3.Font.Color = vbRed

End If

' Determining what color cell is needed (6 refers to the yellow cell color)

If (myWS.Range("B2").Interior.ColorIndex = 6 And myWS.Range("A2").Value > 0) Then

    cell3.Interior.ColorIndex = 6

End If

End Sub


Sub Clear()

Set myWS = ThisWorkbook.Sheets("MySheet")

With myWS.Range("C2")
    .Font.Color = 0
    .Font.Bold = False
    .Interior.ColorIndex = 0
End With

End Sub
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.