Highlight a column when first row is equal to "1"

[arbo80]

Hi all,

I would like to create a macro that highlights multiple columns in Excel when the first row of the column is equal to "1". I was able to find the solution for one column but I would like to do the same for 50 or more columns and I don't want to copy and paste my code. Is there any easier way? Here is my current code for the first column but I need one for 50:

Sub colorColumn()
If (Range("A1").Value = "1") Then
Range("A2:A50").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Else
Range("A2:A50").Select
Selection.Interior.ColorIndex = xlNone
End If
End Sub

Thanks,

Armel

 

[AnotherHiggins]

No VBA required.

See Format > Conditional Formatting. Check out Excel's help file entry for it.

thread68-1484508 might be of some help to you.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

Don't need VBA to do this. Check out Format > Conditional Formatting

Select ALL the cells you want to format before you Format > Conditional Formatting.

Enter the formula in CF as if you are in the ActiveCell. Will be applied to the entire range.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[arbo80]

Thanks guys for your help! but how do I make sure that it highlights the entire column instead of just the cell?

 

[SkipVought]

"Select ALL the cells you want to format before you Format > Conditional Formatting."

Make sure that the reference is ABSOLUTE to Row 1 and relative to the column. ie =A$1=1 if your activecell is in column A.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[arbo80]

Thanks! It works fine now!