excel vba: fill another column with color if this column is not null-CodePudding

how to write code in vba if I want the another column to fill with yellow color when one column is not null?

For example:

if A1,A3,A8,A100 is not null:
fill background of B1,B3,B8,B100 into yellow color

If a loop is used would be great because my actual case have 7000 cells to fill instead of 4

CodePudding user response：

Option Explicit

Sub ColorColA()

    Dim ws As Worksheet
    Dim lastrow As Long, cell As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastrow = ws.Range("B" & Rows.Count).End(xlUp).Row
    For Each cell In ws.Range("A1:A" & lastrow)
        If IsEmpty(cell) Then
            cell.Offset(0, 1).Interior.Color = RGB(255, 255, 0) 'yellow
        Else
            cell.Offset(0, 1).Interior.Pattern = xlNone ' remove color
        End If
    Next
    MsgBox lastrow & " rows scanned", vbInformation

End Sub