CODEWITHSUNDEEP
excelvba
Suliman
Suliman

Reputation: 1509

Compare the nearest cells

I need to compare one cell with next and if next is greater more than 3 than first, than to make it's color. example: 1 2 6 3 2 8

1 compare with 2 = do not do nothing
2 compare with 6 = make it's color
6 compare with 3 = make it's color to
3 compare with 2 = do not do nothing
2 compare with 8 = make it's color.

Here is code that make cells less then 4 color, but I can't understand how to diff one cell with next :(

Sub Color()
Dim i As Integer
For i = 1 To 7
    With ActiveSheet.Cells(i)
    If .Value < 4 Then
    .Interior.Color = QBColor(10)
    End If
    End With
    Next i
End Sub

Upd:

Oh! Look like I have found solution!

Sub Color()
Dim i As Integer
For i = 1 To 7
    With ActiveSheet.Cells(i)
    If ActiveSheet.Cells(i) < ActiveSheet.Cells(i + 1) Then
    ActiveSheet.Cells(i + 1).Interior.Color = QBColor(10)
    End If
    End With
    Next i
End Sub

Upvotes: 0

Views: 131

Answers (1)

brettdj
brettdj

Reputation: 55692

You could use conditional formatting for this rather than VBA, Debra covers this topic thoroughly here, http://www.contextures.com/xlcondFormat01.html

In your case:

  1. Select A1:E1
  2. Conditional Formatting ... New Rule (different menu options depending on your Excel version)
  3. Use a formula to determine what cells to format
  4. use =B1-A1>3 to add a relative formula
  5. Pick a fill colour

screenshot from xl2010 below example

Upvotes: 2

Related Questions