set up a macro in Excel to color cells when clicked

[Guest]

I would liketo create a spreasheet that will highlight the value in the first
column if there ia value in another column of the same row.

 

[Bob Phillips]

Sub Test
Dim iLastRow As Long
Dim i As Long

For i = 1 To UsedRange.Rows.Count
If Application.Count(Range("B" & i).Resize(,255)) > 0 Then
Cells(i,"A").Interior.Colorindex = 36
End If
Next i

End Sub

 

[Guest]

Could you give me a little more instructions with this Macro? Where am I
suppose to insert the lines to be used?

Thanks.
rebecca

 

[Bob Phillips]

Hi Rebecca,

Thinking about this, I now think it is better to use conditional formatting.

Select A1 through to An for all relevant rows

Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add this formula =COUNTA(B1:IV1)>0
Click Format
Select the pattern tab
Choose an acceptable highlight colour
OK
OK

Should now work okay