+ Reply to Thread
Results 1 to 13 of 13

Conditional formatting VBA not working

Hybrid View

  1. #1
    Registered User
    Join Date
    02-26-2013
    Location
    Engerland
    MS-Off Ver
    Excel 20011
    Posts
    13

    Conditional formatting VBA not working

    I have a whole range of cells that require conditional formatting but for testing purposes have just the two. Cell E15 has a drop-down list which when one of the entries is selected, colours E16, the cell underneath it. The code for doing this is below. The issue is, it doesn't colour the cell below and it's probably some really daft mistake that I can't see.
    Many thanks
    ArtySin

    Private Sub Projects(ByVal source As Range)
    
    Dim source
    'Source.Range ("E15,G15,I15,K15,M15,O15,Q15,S15,U15,W15")
    source.Range ("E15")
    
    Dim Bar
    'Bar.Range ("E16,G16,I16,K16,M16,O16,Q16,S16,U16,W16")
    Bar.Range ("E16")
    
    If source <> 0 Then
    
        Application.EnableEvents = False
        Select Case source.Value
            Case "AMD SAC"
                Bar.Interior.ColorIndex = 3 'Red
            Case "T1 Transfers"
                Bar.Interior.ColorIndex = 5 'Blue
            Case "T2A Arrivals"
                Bar.Interior.ColorIndex = 6 'Yellow
            Case "T3IB Main"
                Bar.Interior.ColorIndex = 4 'Green
            Case "T4 SAC"
                Bar.Interior.ColorIndex = 7 'Magenta
            Case "T5 WBU"
                Bar.Interior.ColorIndex = 8
            Case "Holiday"
                Bar.Interior.ColorIndex = 9
        End Select
        Application.EnableEvents = True
    End If
    End Sub

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,155

    Re: Conditional formatting VBA not working

    You need to define the variables as ranges and use Set:

    Dim Source As Range
    'Set Source = Range("E15,G15,I15,K15,M15,O15,Q15,S15,U15,W15")
    Set source = Range("E15")
    
    Dim Bar As Range
    'Set Bar = Range("E16,G16,I16,K16,M16,O16,Q16,S16,U16,W16")
    Set Bar = Range("E16")

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Conditional formatting VBA not working

    I'm a little lost as to the layout you are using..
    you pass source as a range and then have source.range("E15")?

    I'm thinking the best way to do this would be with a worksheet change event with code like:
    Private Sub Worksheet_Change(Byval target as ref)
    If target.row = 15 then
    Application.EnableEvents = False
        With target.offset(1,0).interior
        Select Case source.Value
            Case "AMD SAC"
                .ColorIndex = 3 'Red
            Case "T1 Transfers"
                .ColorIndex = 5 'Blue
            Case "T2A Arrivals"
                .ColorIndex = 6 'Yellow
            Case "T3IB Main"
                .ColorIndex = 4 'Green
            Case "T4 SAC"
                .ColorIndex = 7 'Magenta
            Case "T5 WBU"
                .ColorIndex = 8
            Case "Holiday"
                .ColorIndex = 9
        End Select
        end with
        Application.EnableEvents = True
    End If
    End Sub
    Needs to be in the sheet object within your vba project.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,652

    Re: Conditional formatting VBA not working

    This uses a the Test macro to call the Projects procedure.

    Sub test()
        Projects Range("E15")
    End Sub
        
        
    Private Sub Projects(ByVal source As Range)
        
        Dim CI As Integer  'Interior color index
        
        Select Case source.Value
            Case "AMD SAC": CI = 3 'Red
            Case "T1 Transfers": CI = 5 'Blue
            Case "T2A Arrivals": CI = 6 'Yellow
            Case "T3IB Main": CI = 4 'Green
            Case "T4 SAC": CI = 7 'Magenta
            Case "T5 WBU": CI = 8
            Case "Holiday": CI = 9
            Case Else: CI = xlNone
        End Select
        
        Application.EnableEvents = False
            source.Offset(1).Interior.ColorIndex = CI
        Application.EnableEvents = True
        
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  5. #5
    Registered User
    Join Date
    02-26-2013
    Location
    Engerland
    MS-Off Ver
    Excel 20011
    Posts
    13

    Re: Conditional formatting VBA not working

    Guys,
    I've taken the above suggestions but still no go. It's in the code for sheet1 and I've altered it as per TMShucks post so that it now reads as below. Thanks for the help so far.

    Private Sub Projects(ByVal Source As Range)
    Dim Source As Range
    Set Source = Range("E15")
    Dim Bar As Range
    Set Bar = Range("E16")
    
    If Source <> 0 Then
    
        Application.EnableEvents = False
        Select Case Source.Value
            Case "AMD SAC"
                Bar.Interior.ColorIndex = 3 'Red
            Case "T1 Transfers"
                Bar.Interior.ColorIndex = 5 'Blue
            Case "T2A Arrivals"
                Bar.Interior.ColorIndex = 6 'Yellow
            Case "T3IB Main"
                Bar.Interior.ColorIndex = 4 'Green
            Case "T4 SAC"
                Bar.Interior.ColorIndex = 7 'Magenta
            Case "T5 WBU"
                Bar.Interior.ColorIndex = 8
            Case "Holiday"
                Bar.Interior.ColorIndex = 9
        End Select
        Application.EnableEvents = True
        
    End If
    End Sub

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,652

    Re: Conditional formatting VBA not working

    Quote Originally Posted by ArtySin View Post
    Guys,
    I've taken the above suggestions but still no go. It's in the code for sheet1 and I've altered it as per TMShucks post so that it now reads as below. Thanks for the help so far.

    Private Sub Projects(ByVal Source As Range)
    Dim Source As Range
    Set Source = Range("E15")
    You cannot declare the range variable Source twice in the same procedure. Remove the Dim statement.

  7. #7
    Registered User
    Join Date
    02-26-2013
    Location
    Engerland
    MS-Off Ver
    Excel 20011
    Posts
    13

    Re: Conditional formatting VBA not working

    Tried that AlphaFrog but still no go. I tried doing that and then also removing the "Dim Bar as Range" so that the code now reads as below but as I mentioned still no go.
    Thanks in advance

    Private Sub Projects(ByVal Source As Range, Bar As Range)
    
    Set Source = Range("E15")
    Set Bar = Range("E16")
    
    If Source <> 0 Then
    
        Application.EnableEvents = False
        Select Case Source.Value
            Case "AMD SAC"
                Bar.Interior.ColorIndex = 3 'Red
            Case "T1 Transfers"
                Bar.Interior.ColorIndex = 5 'Blue
            Case "T2A Arrivals"
                Bar.Interior.ColorIndex = 6 'Yellow
            Case "T3IB Main"
                Bar.Interior.ColorIndex = 4 'Green
            Case "T4 SAC"
                Bar.Interior.ColorIndex = 7 'Magenta
            Case "T5 WBU"
                Bar.Interior.ColorIndex = 8
            Case "Holiday"
                Bar.Interior.ColorIndex = 9
        End Select
        Application.EnableEvents = True
        
    End If
    End Sub

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,652

    Re: Conditional formatting VBA not working

    Quote Originally Posted by ArtySin View Post
    Tried that AlphaFrog but still no go. I tried doing that and then also removing the "Dim Bar as Range" so that the code now reads as below but as I mentioned still no go.
    Thanks in advance

    Private Sub Projects(ByVal Source As Range, Bar As Range)
    
    Set Source = Range("E15")
    Set Bar = Range("E16")
    
    If Source <> 0 Then
    
        Application.EnableEvents = False
        Select Case Source.Value
            Case "AMD SAC"
                Bar.Interior.ColorIndex = 3 'Red
            Case "T1 Transfers"
                Bar.Interior.ColorIndex = 5 'Blue
            Case "T2A Arrivals"
                Bar.Interior.ColorIndex = 6 'Yellow
            Case "T3IB Main"
                Bar.Interior.ColorIndex = 4 'Green
            Case "T4 SAC"
                Bar.Interior.ColorIndex = 7 'Magenta
            Case "T5 WBU"
                Bar.Interior.ColorIndex = 8
            Case "Holiday"
                Bar.Interior.ColorIndex = 9
        End Select
        Application.EnableEvents = True
        
    End If
    End Sub
    Now the question is how are you calling the Projects procedure? It's now expecting two range arguments passed to it. This code is being called by another procedure. The problem may be outside this procedure.

    Also, don't just describe something as no go or doesn't work. If you could explain exactly what happens (error and description, wrong result, nothing) that helps us diagnose the issue.

  9. #9
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Conditional formatting VBA not working

    Did you try the code I posted? there doesn't seem anything wrong with the last bit of code you put. In what way doesn't it work? Are you expecting this to run "automatically" when you change a cell?

  10. #10
    Registered User
    Join Date
    02-26-2013
    Location
    Engerland
    MS-Off Ver
    Excel 20011
    Posts
    13

    Re: Conditional formatting VBA not working

    Sorry Guys,
    Yes I should have been more specific (slaps own wrist).

    AlphaFrog, I've placed the code in the sheet1 and so expected it to work when I enter one of the values in E15. However, when I do enter one of the values nothing happens other than that value being entered in E16.

    Yudlugar, when I run your code I am expecting it to update automatically and when I tried your code I get: Compile error, User defined type not defined amd it highlights the very first row: Private Sub Worksheet_Change(ByVal target As ref)

    Many thanks

  11. #11
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Conditional formatting VBA not working

    Oooooops should be byval target as range.... sorry!

  12. #12
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,652

    Re: Conditional formatting VBA not working

    Private Sub Worksheet_Change(ByVal Target As Range)
        
        Dim CI As Integer  'Interior color index
        
        If Target.Count > 1 Then Exit Sub
        If Intersect(Range("E15,G15,I15,K15,M15,O15,Q15,S15,U15,W15"), Target) Is Nothing Then Exit Sub
        
        Select Case Target.Value
            Case "AMD SAC": CI = 3 'Red
            Case "T1 Transfers": CI = 5 'Blue
            Case "T2A Arrivals": CI = 6 'Yellow
            Case "T3IB Main": CI = 4 'Green
            Case "T4 SAC": CI = 7 'Magenta
            Case "T5 WBU": CI = 8
            Case "Holiday": CI = 9
            Case Else: CI = xlNone
        End Select
        
        Application.EnableEvents = False
            Target.Offset(1).Interior.ColorIndex = CI
        Application.EnableEvents = True
        
    End Sub

  13. #13
    Registered User
    Join Date
    02-26-2013
    Location
    Engerland
    MS-Off Ver
    Excel 20011
    Posts
    13

    Re: Conditional formatting VBA not working

    Guys, thanks VERY much for sorting this out for me. Both solutions now working so both please take a bow

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. conditional formatting not working
    By arnab0711 in forum Excel General
    Replies: 3
    Last Post: 06-27-2012, 04:55 AM
  2. [SOLVED] 3 Conditional Formatting - 3rd one not working ?
    By SVTF in forum Excel General
    Replies: 3
    Last Post: 06-07-2012, 12:41 AM
  3. VBA working with conditional formatting
    By MARKSTRO in forum Excel Programming / VBA / Macros
    Replies: 31
    Last Post: 01-12-2012, 05:33 PM
  4. Conditional Formatting Not Working
    By mycon73 in forum Excel General
    Replies: 14
    Last Post: 07-16-2011, 01:39 PM
  5. Replies: 0
    Last Post: 01-20-2009, 05:19 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1