I absolutely cannot get this code to work I've been trying for hrs. It keeps saying block if without end if. Someone who has some knowledge on this subject please help.


Sub forexcolors()
'dimension variable Col and Row
'Select colums 1 to 6
For Col = 1 To 6
'Select Rows 2 to 9
For Row = 2 To 9
Cells(Row, Col).Select
Range("E2").Select
AUD = ActiveCell.Value
If ActiveCell.Value("AUD") Then

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
End With
If ActiveCell.Value("JPY") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
End With
If ActiveCell.Value("CHF") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
End With
If ActiveCell.Value("NZD") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
End With
If ActiveCell.Value("EUR") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
End With
If ActiveCell.Value("USD") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
End With
If ActiveCell.Value("CAD") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
End With
If ActiveCell.Value("GBP") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 6299648

End With
End If
End Sub

I think you used a bunch of "End With" when you meant to use "End If"

Nah its not that I just went through and replaced them all and it still didnt work.

I can guarantee you missed one. That's what the error is telling you.

ok so does this look right?

Sub forexcolors()
'dimension variable Col and Row
'Select colums 1 to 6
For Col = 1 To 6
'Select Rows 2 to 9
For Row = 2 To 9
Cells(Row, Col).Select
Range("E2").Select
AUD = ActiveCell.Value
If ActiveCell.Value("AUD") Then

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
End If
If ActiveCell.Value("JPY") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
End If
If ActiveCell.Value("CHF") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
End If
If ActiveCell.Value("NZD") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
End If
If ActiveCell.Value("EUR") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
End If
If ActiveCell.Value("USD") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
End If
If ActiveCell.Value("CAD") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
End If
If ActiveCell.Value("GBP") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 6299648
End If

End Sub

Try this, I just noticed all those with blocks you had going on:

That is the error message i got. "for without next". I just copy and pasted your post into my vba.

Don't ever copy paste code unless you know what it does...

It's telling you that you used a "For" loop without telling it where the end of the loop is.

What are you trying to do here anyway? Looking closer it seems like all you're doing is changing the color based on a value in a cell.

Any reason you're not using conditional formatting?

haha ok there is probably an easier way to do this.

I have to write a program that colors a cell based on the name in the cell.

It keeps saying invalid file when I try to upload it. Is it possible I could send it to your email.

I really appreciate your help btw!

Google around for "conditional formatting". Unless you have a very old version of excel, you should be able to do what you want without any code.