Coloring Cell if Data is present

O

oakman

Greetings,

I have a list of names on my Input Sheet . I have named this cell
range "NAME". Next to each name on the list, i have put a formula to
show the word "PRINT" or leave blank. I have named this cell range
"UNITS". I am trying to color and bold the cells in the "NAME" range
whenever the word "PRINT" appears in the "UNITS" cell range. I have
managed to get this far with code, but I know I am missing something
really inmportant. Any suggetions would be greatly appreciated!

Thanks in advance!

For Each c In Worksheets("Input").Range("UNITS").Cells
If c.Value = "PRINT" Then
With Range("NAME")
Font.Bold = True
Interior.Color = RGB(255, 255, 0)
End With
End If
Next c
End Sub
 
B

Bob Phillips

Oakman,

Try this

Dim c As Range
For Each c In Worksheets("Input").Range("UNITS").Cells
If c.Value = "PRINT" Then
With Range("NAME")(c.Row, 1)
.Font.Bold = True
.Interior.Color = RGB(255, 255, 0)
End With
End If
Next c

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Gretchen

Do you have to do this in VB?

Have you ever checked out Conditional Formatting? It
works well for fairly simple cases. Plus, you don't have
to write more code returning the Name to a default format
if "Print" goes away.

Say you have Names in A1:A9 and Units in B1:B9. Format
cell A1 this way:
Format>Conditional Formatting>
For Condition 1, use "Formula is" and type in =B1="PRINT"
and set the desired cell formats.
Copy this format on down to A9.
 
Top