PDA

View Full Version : Rectification of VBA script



fatalcore
02-01-2012, 03:37 AM
Hi,
I am using a script to basically highlight cells if the calculated value is not equal to the below value, and if the cell below it is blank then do nothing.
Strangely it is working with some value and not working in other value. Can somebody please rectify the code. I don't understand what is wrong in it.
Thanks in advance.
PS-I am attaching a dummy sheet for reference.

Sub TurnCellsYellow()
Dim c As Range
Selection.Interior.ColorIndex = xlNone
For Each c In Selection
If Not IsEmpty(c.Offset(1, 0)) Then
If c.Offset(1, 0) <> c Then c.Interior.ColorIndex = 6
End If
Next c
End Sub

Bob Phillips
02-01-2012, 04:08 AM
Give us an example of where it works, and an example of where it does not.

fatalcore
02-01-2012, 04:37 AM
I have attached a sheet and explained in details.

Here are the steps to replicate the error.
1>Select the highlighted cells in yellow and no fill the color.
2>Select the cells highlighted in red and click the "Check" button.

3>The red cells if equal to the below cell then the cell should not get highlighted else it will be highlighted in yellow. If the below cell is blank then also it is not highlighted.

Thanks in advance.

Bob Phillips
02-01-2012, 04:49 AM
Try this



Sub TurnCellsYellow()
Dim c As Range
Selection.Interior.ColorIndex = xlNone
For Each c In Selection

If Not IsEmpty(c.Offset(1, 0)) Then
If IsNumeric(c.Value) And IsNumeric(c.Offset(1, 0).Value) Then
If Round(c.Offset(1, 0).Value, 2) <> Round(c.Value, 2) Then
c.Interior.ColorIndex = 6
End If
End If
End If
Next c
End Sub

fatalcore
02-01-2012, 04:55 AM
That is perfect xld,
Can you please edit these two code also as they are the same but checks the right and the left.

Sub TurnCellsYellowright()

Dim c As Range

Selection.Interior.ColorIndex = xlNone

For Each c In Selection

If Not IsEmpty(c.Offset(0, 1)) Then
If c.Offset(0, 1) <> c Then c.Interior.ColorIndex = 6
End If

Next c

End Sub


Sub TurnCellsYelloeleft()

Dim c As Range

Selection.Interior.ColorIndex = xlNone

For Each c In Selection

If Not IsEmpty(c.Offset(0, -1)) Then
If c.Offset(0, -1) <> c Then c.Interior.ColorIndex = 6
End If

Next c
End Sub

Bob Phillips
02-01-2012, 05:14 AM
Check my changes to your code and apply the same logic.