Excel - If statement

  • Thread starter Thread starter learning_codes
  • Start date Start date
L

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
 
(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 ?
Click to expand...

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.
 
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
 
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.
Click to expand...

Thanks for catching that, Ron! You're absolutely right, as per
usual.<g>
 
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
Click to expand...

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
 
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
Click to expand...

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.
 
Select A1:S100 instead of entire rows from 1 to 100

Same rules apply.


Gord Dibben MS Excel MVP
 
Back
Top