Conditional Format - Doing without using .Select?

[pallaver]

Currently I have a conditional format statement which uses select in
order to complete the cell CF.
The problem I have is that I have another macro which runs
automatically when Sheet1 is opened, thus in the below, I get an error
b/c the macro on Sheet1 runs.

Is there a way to CF without using .Select?

--------------------------------------

Sheets("Sheet1").Select
Sheets("Sheet1").Cells(TemporaryRow + (RowTest - 1), ItemColumn +
2).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, Formula1:=XStanleyVariance + 0.008
Selection.FormatConditions(1).Font.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlLess, Formula1:=XStanleyVariance - 0.008
Selection.FormatConditions(2).Interior.ColorIndex = 33
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, Formula1:=XStanleyVariance - 0.008,
Formula2:=XStanleyVariance + 0.008
Selection.FormatConditions(3).Interior.ColorIndex = xlNone
Selection.FormatConditions(3).Font.ColorIndex = xlAutomatic
Sheets("´£¥X¥Î").Select

--------------------------------------

I tried the following, but it didn't work for some reason.


Sheets("Sheet1").Cells(TemporaryRow + (RowTest - 1), ItemColumn +
2).FormatConditions.Delete
Sheets("Sheet1").Cells(TemporaryRow + (RowTest - 1), ItemColumn +
2).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater,
Formula1:=XStanleyVariance + 0.008
Sheets("Sheet1").Cells(TemporaryRow + (RowTest - 1), ItemColumn +
2).FormatConditions(1).Font.ColorIndex = 3
Sheets("Sheet1").Cells(TemporaryRow + (RowTest - 1), ItemColumn +
2).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
Formula1:=XStanleyVariance - 0.008
Sheets("Sheet1").Cells(TemporaryRow + (RowTest - 1), ItemColumn +
2).FormatConditions(2).Interior.ColorIndex = 33
Sheets("Sheet1").Cells(TemporaryRow + (RowTest - 1), ItemColumn +
2).FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween,
Formula1:=XStanleyVariance - 0.008, Formula2:=XStanleyVariance + 0.008
Sheets("Sheet1").Cells(TemporaryRow + (RowTest - 1), ItemColumn +
2).FormatConditions(3).Interior.ColorIndex = xlNone
Sheets("Sheet1").Cells(TemporaryRow + (RowTest - 1), ItemColumn +
2).FormatConditions(3).Font.ColorIndex = xlAutomatic


Any help appreciated, thankx. -np

 

[FSt1]

hi,
yes. seldom do you need to use the select command. just use a variable.
dim cf as range
set cf = sheets("sheet1").cells(your range)
cf.FormatConditions.Delete
cf.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:=XStanleyVariance + 0.008
cf.FormatConditions(1).Font.ColorIndex = 3
cf.etc.etc.etc

hope this helps
Regards
FSt1

 

[pallaver]

Thanks. I guess that's what I get for using the record macro function
to figure out VBA commands....