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.

 

[Ron Rosenfeld]

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

If you want the color to switch on and off depending on whether there is an X in the cell, it might work better to set a conditional format. To do this in code, for the range you mention, something like:

=================
Option Explicit
Sub FmtBlueRow()
Dim rg As Range
Dim fc As Object
Const sFormula1 As String = "=find(""X"",$C1)"
Set rg = Range("$1:$2000")
rg(1, 3).Activate
With rg.FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:=sFormula1
End With

rg.FormatConditions(1).Interior.ColorIndex = 34

End Sub
===========================

A few notes:

1. This deletes all conditional formats that apply to this range. If you have other CF's in that range, you might want to preserve them; or you may be able to test to see if this format is already there.
2. As written, the conditional format formula is case-sensitive, and will trigger if there is an "X" any where in the cell. But it can be modified to make it both case insensitive, and also to require that "X" be the only thing in the cell.

 

[Ron Rosenfeld]

If you want the color to switch on and off depending on whether there is an X in the cell, it might work better to set a conditional format. To do this in code, for the range you mention, something like:

=================
Option Explicit
Sub FmtBlueRow()
Dim rg As Range
Dim fc As Object
Const sFormula1 As String = "=find(""X"",$C1)"
Set rg = Range("$1:$2000")
rg(1, 3).Activate
With rg.FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:=sFormula1
End With

rg.FormatConditions(1).Interior.ColorIndex = 34

End Sub
===========================

There's an extra line in the above from a previous iteration. It should be:

=====================
Option Explicit
Sub FmtBlueRow()
Dim rg As Range
Const sFormula1 As String = "=find(""X"",$C1)"
Set rg = Range("$1:$2000")
rg(1, 3).Activate
With rg.FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:=sFormula1
End With

rg.FormatConditions(1).Interior.ColorIndex = 34

End Sub
=======================

 

[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

 

[Ron Rosenfeld]

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

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.

 

[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