Conditional Formatting Criteria

[Manikandan Arumugam]

Hi Experts,

I have excel sheet with multiple nonblank and blank cells. I need cell color to be highlighted when we enter the value in empty cells. Attached the sample input and manually mocked output in sample file. Please help me on this!!

Thanks in advance.

[NickyC]

Hi
if what you are trying to do is to change a cell's fill when you type in a value in that cell if it was previously blank, then I don't think you can do that with conditional formatting as that only relates to current values. You can approximate it with VBA for the sheet you are working in with selection change and worksheet change events, though I must admit I don't like these as they tend to be unstable.

Try this and see if it works

right click on the name tab of the sheet you at working in and select View Code, then past this code on the VBA sheet. It should change the colour of every cell changed from blank to a value

Dim OldVal

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
OldVal = Target.Value
End Sub

Public Sub Worksheet_Change(ByVal Target As Range)
If OldVal = "" And Target <> "" Then Target.Interior.Color = RGB(255, 130, 50)
End Sub

[Quivolt]

Hi ... try this code which also resets to normal cell when you delete the wrongly entered data in cells......... and only when you enter data it colors it yellow.....

Private Sub Worksheet_Change(ByVal Target As Range)
Set MyRange = Range("A1:A1000")
    For Each Cell In MyRange
        If Cell.Value < 1 Then
        Cell.Interior.ColorIndex = x1None
        End If

        If Cell.Value >= 1 Then
        Cell.Interior.ColorIndex = 6
        End If
    Next

Set MyRange2 = Range("B1:B1000")
    For Each Cell In MyRange2
        If Cell.Value < 1 Then
        Cell.Interior.ColorIndex = x1None
        End If

        If Cell.Value >= 1 Then
        Cell.Interior.ColorIndex = 6
        End If
    Next

Set MyRange3 = Range("C1:C1000")

     For Each Cell In MyRange3
        If Cell.Value < 1 Then
        Cell.Interior.ColorIndex = x1None
        End If

        If Cell.Value >= 1 Then
        Cell.Interior.ColorIndex = 6
        End If
     Next

End Sub