check for blank cell in a particular column and depending on that make changes in other cell

asmi_1758

New Member
Joined
Jan 20, 2021
Messages
1
Office Version
  1. 2016
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.

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
 

Attachments

  • excel1.png
    excel1.png
    16.3 KB · Views: 4

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top