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.

 

[Trevor G]

This will work for you.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ranCol()<br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("A1:IV600")<br>    <SPAN style="color:#00007F">If</SPAN> cell.Value = "x" <SPAN style="color:#00007F">Then</SPAN><br>    cell.Interior.ColorIndex = 3<br>    <SPAN style="color:#00007F">ElseIf</SPAN> cell.Value = "d" <SPAN style="color:#00007F">Then</SPAN><br>    cell.Interior.ColorIndex = 6<br>    <SPAN style="color:#00007F">ElseIf</SPAN> cell.Value = " " <SPAN style="color:#00007F">Then</SPAN><br>    cell.Interior.ColorIndex = 4<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Dan Waterloo]

Try

Sub Cell_Color()
Dim ws As Worksheet
Dim c As Range

Set ws = ActiveSheet

For Each c In ActiveSheet.Range("B2:J10")
    If c.Value = "x" Then c.Interior.ColorIndex = 3
    If c.Value = "d" Then c.Interior.ColorIndex = 6
    If c.Value = " " Then c.Interior.ColorIndex = 4
Next c

End Sub

Note: this change the color of each cell being tested rather than the single cell A1.

 

[Dan Waterloo]

You could also generalize the range of cells you are testing by saying

 For Each c in Selection

 

[pgc01]

Hi

Remark: you don't need code for this, you can use Conditional Formatting directly in the worksheet.

 

[LuxExcel]

This will work for you.


Sub ranCol()
Dim cell As Range
For Each cell In Range("A1:IV600")
If cell.Value = "x" Then
cell.Interior.ColorIndex = 3
ElseIf cell.Value = "d" Then
cell.Interior.ColorIndex = 6
ElseIf cell.Value = " " Then
cell.Interior.ColorIndex = 4
End If
Next
End Sub


Hi, I am facing a similar problem.

Starting from the mentioned code, could it be possible to make something like
If cell.Value = value of the cell to its left Then...?

So that, any cell which has the same value as the one on its left, will be highlighted with a color?

Thats my code which does not work:

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


Many thanks in advance!

 

[Peter_SSs]

As pgc01 pointed out in the old thread, you don't need vba to achieve this result. Is there some particular reason you couldn't use Excel's standard Conditional Formatting?

 

[LuxExcel]

Following context:

I am building a data base in Excel which should show official valuations of a security at a certain quarter in time. However, there might be a quarter where no official valuation is available. In such a situation, the valuation should be equal to the previous quarter and it should be highlighted.

If I use duplicate values (conditional formatting)for example, it would work out. However it would highlight all cells having the same value even if they are not be equal to its previous quarter at that point in time.

As a consequence, it should only refer to its left cell (previous quarter) and it should auto-adjust cell per cell:
Look up if C3=B3, D3=C3... and so on for a few hundred cell values.

I did not find any possibility to make it as precise using conditional formatting. If you have an idea, please enlighten me

 

[Peter_SSs]

I did not find any possibility to make it as precise using conditional formatting. If you have an idea, please enlighten me

See if this pretty simple CF formula does what you want.
Column A can never be highlighted, so select from B2 down to J11 and apply the CF formula shown. Use New Rule -> Use a formula to determine ...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1
2	7	5	6	3	3	7	1	7	8	7
3	1	4	8	8	4	9	8	1	9	4
4	5	7	1	6	5	3	6	6	3	3
5	8	8	6	9	9	9	9	9	3	5
6	1	9	7	1	6	1	1	8	3	1
7	3	4	3	9	9	4	3	2	2	6
8	4	4	7	3	6	2	2	6	1	5
9	9	3	8	4	3	9	6	6	4	1
10	6	7	9	8	1	5	9	4	7	5
11	5	5	4	4	3	1	3	9	1	4
CF Left

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =B2=A2	Abc

 

[LuxExcel]

Unfortunately I do not get along about how to enter the formula.
My conditional formatting box seems to be different than yours? I am using EXCEL 2013

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


How should it be entered in my layout?

Thank you!