CODEWITHSUNDEEP
vbaexcel
user3596788
user3596788

Reputation: 95

Excel VBA Fill cell color based on value in different cell

I'm looking to modify my code to only fill cells in red, if the value = 0 and the adjacent cell value in column L is empty.

For cells with a value = 0 and the adjacent cell value in column L is not empty, I would like to fill the 0 cell with a pattern.

I attached a screenshot of how it should look.

enter image description here

Sub Fill_Cell()

    Dim rngCell As Range

    For Each rngCell In Range("C7:K100")
        If rngCell.Value = "0" Then
        rngCell.Cells.Interior.ColorIndex = 3

        Else
        ''If value = 0 & adjacent cell in column L <> ''
        ''rngCell.Cells.Interior.Pattern = xlGray16
        End If
    Next

End Sub

Upvotes: 1

Views: 6224

Answers (3)

Nulled
Nulled

Reputation: 302

It seems like a couple of people have beaten me:

   For Each rngCell In Range("C7:K100")
        If rngCell.Value = "0" And Cells((rngCell.Row), 12).Value <> "" Then
            rngCell.Cells.Interior.Pattern = xlGray16
        ElseIf rngCell.Value = "0" Then
            rngCell.Cells.Interior.ColorIndex = 3
        End If
    Next

Upvotes: 1

Brian
Brian

Reputation: 2108

Try this

Option Explicit

Sub Fill_Cell()

Dim rngCell As Range

For Each rngCell In Range("C7:K100")
    If rngCell.Value = "0" Then
        Select Case Cells(rngCell.row, "L")
            Case Is = ""
                rngCell.Cells.Interior.ColorIndex = 3
            Case Else
                rngCell.Cells.Interior.Pattern = xlGray16
        End Select
    End If
Next rngCell

End Sub

Upvotes: 1

PartyHatPanda
PartyHatPanda

Reputation: 724

You are close! I would try to take your code and modify it just a bit

Sub Fill_Cell()

Dim rngCell As Range

For Each rngCell In Range("C7:K100")
    If rngCell.Value = "0" Then
        If Cells(rngCell.Row, 12).Value <> "" Then
            'If it is 0 and L not empty
            rngCell.Cells.Interior.Pattern = xlGray16
        else
            'If it is 0 and L is empty
            rngCell.Cells.Interior.ColorIndex = 3
        End If
    End If
Next

End Sub

Upvotes: 1

Related Questions