Conditional Formating

[Chandresh]

HI All,

I want to do the following things with the help of conditional formatting.

1) if cell is blank - no colour
2) if cell value is greater than 5% cell should be blue
3) if cell value is less than -5% cell should be blue

I am using below code however I am not getting the results , could you please help.

Sub SpecCheckred()
Sheets("Summary").Select
Dim iRow As Range
Set iRow = Range("b4:BH50000")
For Each cell In iRow
If cell.Value <> "" And cell.Value > 0.05 And cell.Value < -0.05 Then
cell.Interior.Color = RGB(22, 255, 255)
End If
Next
End Sub



Thanks
Chan

 

[Peter_SSs]

HI All,

I want to do the following things with the help of conditional formatting.

1) if cell is blank - no colour
2) if cell value is greater than 5% cell should be blue
3) if cell value is less than -5% cell should be blue

I am using below code however I am not getting the results , could you please help.

Sub SpecCheckred()
Sheets("Summary").Select
Dim iRow As Range
Set iRow = Range("b4:BH50000")
For Each cell In iRow
If cell.Value <> "" And cell.Value > 0.05 And cell.Value < -0.05 Then
cell.Interior.Color = RGB(22, 255, 255)
End If
Next
End Sub



Thanks
Chan

It is impossible for the red part to ever be true. A value cannot be both greater than 0.05 And less than -0.05 at the same time. Therefore, the blue code will never be executed.
Try

If cell.Value <> "" And (cell.Value > 0.05 Or cell.Value < -0.05) Then

 

[Chandresh]

hi,

if I use below code my entire range becomes blue

If cell.Value <> "" And (cell.Value > 0.05 Or cell.Value < -0.05) Then

thanks
Chan

 

[Peter_SSs]

hi,

if I use below code my entire range becomes blue

If cell.Value <> "" And (cell.Value > 0.05 Or cell.Value < -0.05) Then

thanks
Chan

Not for me. Here is my sample sheet after running the code.

Book1
	B	C	D
4	0.0811	0.0945	0.0408
5		0.0433	0.59
6		0.0647
7	0.0026	0.0641	0.0884
8	26	0.0258	0.0015
9	0.1	0.034	0.0245
10	0.0809	0.0232	0.0623
Summary

Are you sure that your data is numerical, not text?