test a range of cells with if statement in vba

[dcasebolt]

Hello,
I am trying to figure out how to test some cells in excel using VBA code to change a color of a cell based on the results of a test. I have it figured out for one cell but have been unable to figure out how to test multiple cells using the same format. Using Excel 2007 software. Tried using range and keep getting a type mismatch 13 error. Used both Range("A1:B7") and [A1:B7] with no luck. Below is my code that works with one cell being tested:

Sub Cell_Color()
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")

If Cells(2, 2).value = "x" Then Cells(1, 1).Interior.ColorIndex = 3
If Cells(2, 2).value = "d" Then Cells(1, 1).Interior.ColorIndex = 6
If Cells(2, 2).value = " " Then Cells(1, 1).Interior.ColorIndex = 4
End Sub

The goal is to test multiple cells for same values and have each one independently respond with the appropriate color code.

 

[Dan Waterloo]

I agree with Peter's conditional formatting solution above as the most straightforward way.

If you really want VBA for this purpose, then try this:

Sub FormatCells()

Dim cell As Range
    For Each cell In Range("B3:AU110")
        If cell.Value = cell.Offset(0, -1).Value Then
            cell.Interior.ColorIndex = 10
        Else
            cell.Interior.ColorIndex = 0
        End If
    Next
End Sub

 

[Peter_SSs]

There is only field mentioning:
"Format values where this formula is true"

That is exactly where you put the formula that I gave you.

However, if your data is in a different position in the sheet than mine, the formula will need adjusting.

Critical things are:

1. Select from the top cell in column 2 of the data down to the bottom right of the data.
2. get into that input box you mentioned and the two cell references in the formula should be the top left cell selected in point 1. and the cell immediately to its left.
3. Click Format... to select your colour from the Fill tab & OK till you exit the dialog.

 

[LuxExcel]

I agree with Peter's conditional formatting solution above as the most straightforward way.

If you really want VBA for this purpose, then try this:

Sub FormatCells()

Dim cell As Range
    For Each cell In Range("B3:AU110")
        If cell.Value = cell.Offset(0, -1).Value Then
            cell.Interior.ColorIndex = 10
        Else
            cell.Interior.ColorIndex = 0
        End If
    Next
End Sub

Hi thank you for your solution. I've done this before and it works.

Many thanks for that. Now I am trying to check if Peter_SSs solution is more appropriate.

Kind regards!

 

[Peter_SSs]

If you want, the CF can be applied by vba as well. One advantage of CF is that it automatically updates if any of the cell values change.

In this code, once again the relevant range to input is the data range, excluding the first column.

Sub CFLeft()
  With Range("B2:J11")  '<- Data range, excluding the first column
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Cells(1, 1).Address(0, 0) & "=" & .Cells(1, 0).Address(0, 0)
    .FormatConditions(1).Interior.Color = vbGreen
  End With
End Sub

 

[renicemae]

This will work for you.

Your solution worked for me. Thank you so much.