+ Reply to Thread
Results 1 to 4 of 4

Conditional Format Based on Cell Value - Getting Method Range of object Global failed erro

Hybrid View

  1. #1
    Registered User
    Join Date
    05-22-2021
    Location
    US
    MS-Off Ver
    Excel 365
    Posts
    58

    Conditional Format Based on Cell Value - Getting Method Range of object Global failed erro

    I am trying to apply conditional formatting to a report. Shading will occur for regions and certain cells pertaining to that region that need to be reviewed. Each region and corresponding cells to review are shaded the same color - a different color for each region.

    The code I have below shows a couple of the regions but I am getting a Run-time error - Method Range of object Global failed. I have tried changing the line of code several ways but I haven't had any luck. And as you will see below, there are many lines written exactly the same so while it is stopping near the top, this error really occurs throughout. I realize that I can record this but I am trying to shorten the code and hopefully it would run more efficiently.

    I would appreciate any guidance or ways this code could be corrected and/or better written. Also, since I still learning, I would appreciate bits of explanation so I can make notes in the code for future use/guidance.

    
        Sub CondFormat()
    '   Apply conditional formatting by Region for items that need review
    
        Dim count, b As Long
        count = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Row   'Column A has no gap in data
        b = 2  'data starts at row 2
        Do While b <= count
    
    '   Region A
        If Cells(b, 1).Value = "Region A" Then
            Range(Cells(b, 1)).Interior.Color = RGB(180, 198, 231)      'Region  This line is where Debug stops
            Range(Cells(b, 7).Cells(b, 7)).Interior.Color = RGB(180, 198, 231)      
            Range(Cells(b, 20).Cells(b, 20)).Interior.Color = RGB(180, 198, 231)    
            Range(Cells(b, 19).Cells(b, 19)).Interior.Color = RGB(180, 198, 231)   
        End If
        
        If Cells(b, 1).Value = "Region A" And Cells(b, 25).Value = "Invalid" Then
            Range(Cells(b, 25).Cells(b, 29)).Interior.Color = RGB(180, 198, 231)   
        End If
           
        If Cells(b, 1).Value = "Region A" And Cells(b, 25).Value = "Invalid " Then   'space after invalid
            Range(Cells(b, 25).Cells(b, 29)).Interior.Color = RGB(180, 198, 231)    
        End If
               
        If Cells(b, 1).Value = "Region A" And Cells(b, 22).Value = 0 Then
            Range(Cells(b, 22).Cells(b, 22)).Interior.Color = RGB(180, 198, 231)    
        End If
            
        If Cells(b, 1).Value = "Region A" And Cells(b, 5).Value = 1 _
            And Cells(b, 31).Value = "No info" Then
            Range(Cells(b, 5).Cells(b, 5)).Interior.Color = RGB(180, 198, 231)      
            Range(Cells(b, 31).Cells(b, 31)).Interior.Color = RGB(180, 198, 231)    
        End If
        
        
    '   Region B
        If Cells(b, 1).Value = "Region B" Then
            Range(Cells(b, 1).Cells(b, 1)).Interior.Color = RGB(248, 203, 173)      
            Range(Cells(b, 7).Cells(b, 7)).Interior.Color = RGB(248, 203, 173)      
            Range(Cells(b, 24).Cells(b, 24)).Interior.Color = RGB(248, 203, 173)   
        End If
    
        If Cells(b, 1).Value = "Region B" And Cells(b, 25).Value = "Invalid" Then
            Range(Cells(b, 25).Cells(b, 29)).Interior.Color = RGB(248, 203, 173)   
        End If
            
        If Cells(b, 1).Value = "Region B" And Cells(b, 25).Value = "Invalid " Then   'space after invalid
            Range(Cells(b, 25).Cells(b, 29)).Interior.Color = RGB(248, 203, 173)   
        End If
                    
        If Cells(b, 1).Value = "Region B" And Cells(b, 22).Value = 0 Then
            Range(Cells(b, 22).Cells(b, 22)).Interior.Color = RGB(248, 203, 173)    
        End If
        
        If Cells(b, 1).Value = "Region B" And Cells(b, 31).Value = "No info" Then
            Range(Cells(b, 31).Cells(b, 31)).Interior.Color = RGB(248, 203, 173)    
        End If
    
        b = b + 1
    
       Loop
    
      End sub
    Again, I appreciate any help provided.
    Thanks.
    Last edited by ellenlewis; 06-07-2023 at 12:58 PM.

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,337

    Re: Conditional Format Based on Cell Value - Getting Method Range of object Global failed

    You need to give Excel a clue as to what sheet it is working on.
    Take care to note where the extra point(full stops) and commas are (they are small but very important.
    I have only correct the small piece of code, you will have to correct all the rest.


    With ActiveSheet
        If .Cells(b, 1).Value = "Region A" Then
            .Cells(b, 1).Interior.Color = RGB(180, 198, 231)      'Region  This line is where Debug stops
            .Range(.Cells(b, 7), .Cells(b, 7)).Interior.Color = RGB(180, 198, 231)
            .Range(.Cells(b, 20), .Cells(b, 20)).Interior.Color = RGB(180, 198, 231)
            .Range(.Cells(b, 19), .Cells(b, 19)).Interior.Color = RGB(180, 198, 231)
        End If
    End With
    just re-read the code - most are single cells so address them as such - rows 4 & 5 can be amalgamated as a range.

    .Range(.Cells(b, 19), .Cells(b, 20)).Interior.Color = RGB(180, 198, 231)
    Last edited by torachan; 06-07-2023 at 01:36 PM.
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  3. #3
    Registered User
    Join Date
    05-22-2021
    Location
    US
    MS-Off Ver
    Excel 365
    Posts
    58

    Re: Conditional Format Based on Cell Value - Getting Method Range of object Global failed

    Thanks so much! This worked and thanks for explaining. Getting the right code is always nice but explaining it is even better!!
    I appreciate your time and help!
    Thanks again!

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,337

    Re: Conditional Format Based on Cell Value - Getting Method Range of object Global failed

    thanks for the feedback and added rep point - glad to have helped.

+ 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. Error Method Range of Object - Global Failed
    By bdrod in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-18-2016, 06:32 PM
  2. [SOLVED] Method Range of object Global failed
    By radddogg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-09-2016, 10:30 AM
  3. [SOLVED] Error - Method 'range of object' - Global failed
    By highlystrung in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-05-2015, 06:18 AM
  4. Autofilling in a macro and it gives Method 'Range' of object 'Global' failed
    By jsmilke in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2014, 03:55 PM
  5. [SOLVED] Method range of object global failed
    By darahsten in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-29-2013, 12:06 AM
  6. Method Range of object global failed
    By paddingtonstation in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-08-2012, 07:33 AM
  7. Run-time error '1004': Method 'Range' of object 'Global' failed
    By djwestholm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-22-2012, 04:52 PM

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