Hello all,
I have written code to highlight blank cells in row and I want to check column "F" is blank and then write column "G" = "Yes" and highlight column "H" with red color. Any help would be appreciated.
I have written code to highlight blank cells in row and I want to check column "F" is blank and then write column "G" = "Yes" and highlight column "H" with red color. Any help would be appreciated.
VBA Code:
Dim Target As Range
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B:N")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
x = Target.Row
y = Target.Column
Set RangeSupp = Target
If Target.value = "Yes" Then
If y = 2 Then
y = y + 1
'---------------------------------------------------------------------
Dim z As String
Dim found As Boolean
'Dim w As Integer
Range(ActiveCell.Address).Select
z = ""
found = False
For Each cell In Range(Cells(x, y), Cells(x, 14))
'Range(Cells(3, i), Cells(24, i))
'Range(Cells(3, i), Cells(24, i))
cell.Select
'If cell.value <> "-" Then
If ActiveCell.value = z Then
found = True
If found = True Then
'--------------------------------------------
f = ActiveCell.Row
' For shifting the cell from dropdown( column B" to column "C")
If IsEmpty(Range(ActiveCell.Address).value) = True Then
Range(ActiveCell.Address).Resize(, 1).Interior.ColorIndex = 6
'--------------------------------------------
ElseIf IsEmpty(Range("C" & ActiveCell.Row).value) = False Then
Range(ActiveCell.Address).Resize(, 1).Interior.ColorIndex = 6
End If
'--------------------------------------------
' MsgBox "Value found in cell " & ActiveCell.Address
Else
MsgBox "Value not found"
End If
End If
'ActiveCell.Offset(1, 0).Select
Next
'---------------------------------------------------------------------
End If
End If
End Sub
Sub LoadHighlighRow(ByRef Target As Range, value As Variant)
If IsEmpty(Range("C" & x).value) = True Then
Range("C" & x).Resize(, x).Interior.ColorIndex = 6
ElseIf IsEmpty(Range("C" & x).value) = False Then
'Range("C" & x).Resize(, 12).Value = ""
Range("C" & x).Resize(, x).Interior.ColorIndex = 6
'End If
'ElseIf y = 7 Then
'If IsEmpty(Range("H" & x).Value) = True Then
'Range("H" & x).Resize(, 1).Interior.ColorIndex = 3
'ElseIf IsEmpty(Range("H" & x).Value) = False Then
'Range("H" & x).Resize(, 1).Value = ""
'Range("H" & x).Resize(, 1).Interior.ColorIndex = 3
'End If
'ElseIf y = 9 Then
'If IsEmpty(Range("J" & x).Value) = True Then
'Range("J" & x).Resize(, 1).Interior.ColorIndex = 8
'ElseIf IsEmpty(Range("J" & x).Value) = False Then
'Range("J" & x).Resize(, 1).Value = ""
'Range("J" & x).Resize(, 1).Interior.ColorIndex = 8
'End If
'End If
ElseIf Target.value = "No" And y = 2 Then
Range("C" & x).Resize(, 12).value = "N/A"
Range("C" & x).Resize(, 12).Interior.ColorIndex = 0
'ElseIf Target.Value = "No" And y = 7 Then
'Range("H" & x).Resize(, 1).Value = "N/A"
'Range("H" & x).Resize(, 1).Interior.ColorIndex = 0
'ElseIf Target.Value = "No" And y = 9 Then
'Range("J" & x).Resize(, 1).Value = "N/A"
'Range("J" & x).Resize(, 1).Interior.ColorIndex = 0
End If
'End If
If Target.Column <> 2 Then
Target.Interior.ColorIndex = xlNone
End If
End Sub