I have this macro
Which changes the cell color based on value
But if i clear the contents by pressing "delete" or similar, it remains the color of the previously entered value
I've tried changing the last IF argument to "", ClearContents and Empty, but no joy.
Any ideas?
Code:
Sub auto_open()
' Run the macro DidCellsChange any time a entry is made in a
' cell in Sheet1.
ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"
End Sub
Sub DidCellsChange()
Dim KeyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
KeyCells = "B3:AD3, B5:AD5, B7:AD7, B9:AD9, B11:AD11, B13:AD13, B15:AD15, B17:AD17, B19:AD19, B21:AD21, B23:AD23, B25:AD25, B27:AD27, B29:AD29, B31:AD31, B33:AD33, B35:AD35, B37:AD37, B39:AD39, B41:AD41, B43:AD43, B45:AD45, B47:AD47"
' If the Activecell is one of the key cells, call the
' KeyCellsChanged macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged
End Sub
Sub KeyCellsChanged()
Dim Cell As Object
' If the values in A11:C11 are greater than 50...
For Each Cell In Range("B3:AD3")
If Cell.Value = "H" Then
' Make the background color of the cell the 3rd color on the
' current palette.
Cell.Interior.ColorIndex = 3
Cell.Font.ColorIndex = 3
Else
If Cell.Value = "T" Then
' Make the background color of the cell the 3rd color on the
' current palette.
Cell.Interior.ColorIndex = 41
Cell.Font.ColorIndex = 41
Else
If Cell.Value = "G" Then
' Make the background color of the cell the 3rd color on the
' current palette.
Cell.Interior.ColorIndex = 4
Cell.Font.ColorIndex = 1
Else
If Cell.Value = "" Then
' Make the background color of the cell the 3rd color on the
' current palette.
Cell.Interior.ColorIndex = xlNone
Cell.Font.ColorIndex = 1
Else
' Otherwise, set the background to none (default).
Cell.Interior.ColorIndex = xlNone
Cell.Font.ColorIndex = 1
End If
End If
End If
End If
Next Cell
End SubWhich changes the cell color based on value
But if i clear the contents by pressing "delete" or similar, it remains the color of the previously entered value
I've tried changing the last IF argument to "", ClearContents and Empty, but no joy.
Any ideas?