Conditional Formatting VBA Question

[ststern45]

Hello everyone,

I have a question about how to use VBA to format a cell based on another range of cells.

For example using the code at the very bottom is pretty close to what I was looking for but need the following:

I have a range of cell U3:FG145 that is either a blank cell or not blank (not blank cell(s) contain a letter and number such as F6)

The second range of data is located in cell range N20:N4219

So this is what I would like to accomplish:

1>Highlight the cells in range U3:FG145 that are blank code: > c.Interior.Color = RGB(0, 0, 0) 'Color Black

2>Highlight the cells in range U3:FG145 that contain the data from cell range N20:N4219 code: > c.Interior.Color = RGB(146, 208, 80) 'Color Light Green

Thanks in advance!!

For Each c In Range("U3:FG145").Cells

If c.Value = "" Then
c.Interior.Color = RGB(0, 0, 0) 'Color Black
Else
c.Interior.Color = xlNone

End If

Next c

 

[MARK858]

For number 1....

Sub FillBlk()
Range("U3:FG145").Interior.ColorIndex = xlNone
Range("U3:FG145").SpecialCells(4).Interior.Color = RGB(0, 0, 0)
End Sub

 

[Fluff]

How about

   Dim Cl As Range
   Dim Var As Variant
   For Each Cl In Range("U3:FG145")
      If Cl.Value = "" Then
         Cl.Interior.Color = RGB(0, 0, 0) 'Color Black
      Else
         Var = Application.Match(Cl.Value, Range("N20:N4219"), 0)
         If Not IsError(Var) Then
            Cl.Interior.Color = RGB(146, 208, 80)
         Else
            Cl.Interior.Color = xlNone
         End If
      End If
   Next Cl

 

[ststern45]

OK Super!!

Works great.

Thanks for all your help!!

 

[ststern45]

Just a quick question.

How would I apply Bold to these cells?

Thanks

 

[ststern45]

OK I figured it out.

 

[Fluff]

OK Super!!

Works great.

Thanks for all your help!!

Glad we could help & thanks for the feedback

OK I figured it out.

Well done