Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2011
    Posts
    5

    Unanswered: HELP VBA "Block If Without End If"

    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

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I think you used a bunch of "End With" when you meant to use "End If"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2011
    Posts
    5

    hmm

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

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I can guarantee you missed one. That's what the error is telling you.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Oct 2011
    Posts
    5

    getting close?

    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

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try this, I just noticed all those with blocks you had going on:

    Code:
    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
    
    With Selection.Interior
    	If ActiveCell.Value("AUD") Then
    		.Pattern = xlSolid
    		.PatternColorIndex = xlAutomatic
    		.Color = 65535
    	End If
    	If ActiveCell.Value("JPY") Then
    		.Pattern = xlSolid
    		.PatternColorIndex = xlAutomatic
    		.Color = 255
    	End If
    	If ActiveCell.Value("CHF") Then
    		.Pattern = xlSolid
    		.PatternColorIndex = xlAutomatic
    		.Color = 49407
    	End If
    	If ActiveCell.Value("NZD") Then
    		.Pattern = xlSolid
    		.PatternColorIndex = xlAutomatic
    		.Color = 65535
    	End If
    	If ActiveCell.Value("EUR") Then
    		.Pattern = xlSolid
    		.PatternColorIndex = xlAutomatic
    		.ThemeColor = xlThemeColorAccent3
    	End If
    	If ActiveCell.Value("USD") Then
    		.Pattern = xlSolid
    		.PatternColorIndex = xlAutomatic
    		.Color = 5287936
    	End If
    	If ActiveCell.Value("CAD") Then
    		.Pattern = xlSolid
    		.PatternColorIndex = xlAutomatic
    		.Color = 15773696
    	End If
    	If ActiveCell.Value("GBP") Then
    		.Pattern = xlSolid
    		.PatternColorIndex = xlAutomatic
    		.Color = 6299648
    	End If
    End With
    End Sub
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Oct 2011
    Posts
    5

    "for without next"

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

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Oct 2011
    Posts
    5

    vba

    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!

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •