Cell color changes depending on another cell value

[neobad]

Hi,

I have applied formula for "Escalation" column on the bases of "Days" column. So when no. changes in "Days" column, it will automatically update "Escalation" column also.
I need help with following rules -
1. I would like to change the color of "Escalation" column cell to red once its value changes, as well as automatically it writes "No" in "Mail sent" Column.
2. Once I write "yes" in "Mail sent" column, "Escalation" column cell color also must get changed to green.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, an image is not a great way to give us sample data - we can't copy it or see what columns/rows it is in or see any formulas etc.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

In any case see if the vba Worksheet_Change event code is headed in the right direction. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1. Check/edit the column letters in the code.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Const DaysCol As String = "A"
  Const EscalationCol As String = "B"
  Const MailSentCol As String = "C"
  
  Set Changed = Intersect(Target, Columns(DaysCol))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then
        c.Offset(, 1).Interior.Color = vbRed
        c.Offset(, 2).Value = "No"
      Else
         c.Offset(, 1).Interior.Color = xlNone
         c.Offset(, 2).ClearContents
      End If
    Next c
    Application.EnableEvents = True
  End If
  
  Set Changed = Intersect(Target, Columns(MailSentCol))
  If Not Changed Is Nothing Then
    For Each c In Changed
      If LCase(c.Value) = "yes" Then c.Offset(, -1).Interior.Color = vbGreen
    Next c
  End If
End Sub