I have this code that it is working except the reverting to original format. How I can do that?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCheck As Range
Dim cell As Range
Dim rngOriginalFormat As Range
Dim rngWhiteCells As Range
Dim rngGrayCells As Range
Dim rngGreenFont As Range
' Define the range to check for changes in column D
Set rngCheck = Intersect(Target, Me.Columns("D"))
' Exit if no change in column D or multiple cells were changed
If rngCheck Is Nothing Or rngCheck.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
' Loop through each cell in the changed range
For Each cell In rngCheck
' Check if the cell is in one of the specified rows
Select Case cell.row
Case 25
Set rngOriginalFormat = Union(Range("A25:I26"), Range("E25:I32"))
Set rngWhiteCells = Union(Range("E25:I26"), Range("E28:I29"), Range("E31:I32"))
Set rngGrayCells = Union(Range("F27"), Range("F30"))
Set rngGreenFont = Range("F25")
Case 36
Set rngOriginalFormat = Union(Range("A36:D37"), Range("E38:I43"))
Case 44
Set rngOriginalFormat = Union(Range("A44:D45"), Range("E46:I51"))
End Select
' Apply or revert formatting based on the presence of data in column D
If Not IsEmpty(cell) Then
' Apply formatting when data is entered
With rngOriginalFormat
.Font.Color = RGB(128, 128, 128)
.Interior.Color = RGB(214, 217, 219)
End With
Else
' Revert to original formatting when data is removed
With rngOriginalFormat
.Font.Color = vbBlack
.Interior.ColorIndex = xlNone
End With
rngWhiteCells.Interior.Color = RGB(255, 255, 255)
rngGrayCells.Interior.Color = RGB(214, 217, 219)
rngGreenFont.Font.Color = RGB(115, 134, 25)
End If
Next cell
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCheck As Range
Dim cell As Range
Dim rngOriginalFormat As Range
Dim rngWhiteCells As Range
Dim rngGrayCells As Range
Dim rngGreenFont As Range
' Define the range to check for changes in column D
Set rngCheck = Intersect(Target, Me.Columns("D"))
' Exit if no change in column D or multiple cells were changed
If rngCheck Is Nothing Or rngCheck.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
' Loop through each cell in the changed range
For Each cell In rngCheck
' Check if the cell is in one of the specified rows
Select Case cell.row
Case 25
Set rngOriginalFormat = Union(Range("A25:I26"), Range("E25:I32"))
Set rngWhiteCells = Union(Range("E25:I26"), Range("E28:I29"), Range("E31:I32"))
Set rngGrayCells = Union(Range("F27"), Range("F30"))
Set rngGreenFont = Range("F25")
Case 36
Set rngOriginalFormat = Union(Range("A36:D37"), Range("E38:I43"))
Case 44
Set rngOriginalFormat = Union(Range("A44:D45"), Range("E46:I51"))
End Select
' Apply or revert formatting based on the presence of data in column D
If Not IsEmpty(cell) Then
' Apply formatting when data is entered
With rngOriginalFormat
.Font.Color = RGB(128, 128, 128)
.Interior.Color = RGB(214, 217, 219)
End With
Else
' Revert to original formatting when data is removed
With rngOriginalFormat
.Font.Color = vbBlack
.Interior.ColorIndex = xlNone
End With
rngWhiteCells.Interior.Color = RGB(255, 255, 255)
rngGrayCells.Interior.Color = RGB(214, 217, 219)
rngGreenFont.Font.Color = RGB(115, 134, 25)
End If
Next cell
Application.ScreenUpdating = True
End Sub