Excel - If statement

[learning_codes]

Hi,

I need your help. I tried to figure it out how to set the column
"C1:C2000" if there is any "X" letter. If yes, the row show the light
blue colour



If ExcelWorksheet.Range ("C1", "C2000") like "X" then
ExcelWorksheet.Range("A10", "s100").Interior.ColorIndex = 65280
End If


Is there any light blue colour like 65280 ?

Thanks

 

[GS]

(e-mail address removed) laid this down on his screen :

Hi,

I need your help. I tried to figure it out how to set the column
"C1:C2000" if there is any "X" letter. If yes, the row show the light
blue colour



If ExcelWorksheet.Range ("C1", "C2000") like "X" then
ExcelWorksheet.Range("A10", "s100").Interior.ColorIndex = 65280
End If


Is there any light blue colour like 65280 ?

Try...

Dim rng As Range
For Each rng In Range("C1:C2000")
If UCase$(rng.Value) = "X" Then _
rng.EntireRow.Interior.ColorIndex = 41 'light blue
If UCase$(rng.Value) = "X" Then _
rng.EntireRow.Interior.ColorIndex = 37 'pale blue
Next

Comment out (or delete) the line that gives the shade you DON'T want.

 

[GS]

Ron has a good point about using ConditionalFormating. If you want to
apply it to entire rows:

Select entire rows of the range

In the CF dialog:
Choose 'Formula'
Enter: =(UPPER(C1)="X")
On the Pattern tab select the color
OK your way out

 

[GS]

Ron Rosenfeld formulated on Wednesday :

You need to change C1 to $C1; and I believe you also need to have C1 (or at
least a cell in Row 1, as the active cell before entering the CF dialog.

Thanks for catching that, Ron! You're absolutely right, as per
usual.<g>

 

[learning_codes]

Ron Rosenfeld formulated on Wednesday :



Thanks for catching that, Ron! You're absolutely right, as per
usual.<g>

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thank you all .. it works great but there is no way for me to select
range from A1 to S100 instead of entire row colour .

Thanks

 

[GS]

Thank you all .. it works great but there is no way for me to select
range from A1 to S100 instead of entire row colour .

Thanks

Yes, the CF works for the selected area. If you already selected entire
rows then reselect that and delete the CF condition in the CF dialog.

Next select A1:S100 and redo the CF for those cells only.

 

[Gord Dibben]

Select A1:S100 instead of entire rows from 1 to 100

Same rules apply.


Gord Dibben MS Excel MVP