Macro Triggered Using Background Color

[Kinchen]

I had a post for border color and I realize this one is similar, but I didn't realize that on my sheet because the user is zoomed out so far it is useless changing the border color property and linking a macro to it and it is much easier (user wise) to link it to the background color. The coding I have so far does a good job at recognizing the background color in the first box (boxes are 1 column wide and 5 rows long; there are 18 boxes per set) but it doesn't recognize the background colors in the the other boxes (or at least it doesn't link properly to the second part of my macro). The code I have so far is:

Sub Butt*******83()
Dim rng1 As Range
Dim rng2 As Range
Dim B As Long

Set rng1 = Sheets("Layout").Range("C82:C87").Item(1, 1) 
     'first cell in the first box

Set rng2 = rng1.Offset(0, 2 * B)                                      
     'boxes occur every other column

For B = 0 To 17    '18 boxes

If rng2.Interior.Color = RGB(255, 0, 0) Then                       
     'If cell has a red background

Sheets("Reports").Select
Range("A1:J35").Offset(35 * B, 0).EntireRow.Hidden = False 
     'Unhide specific rows in the "Reports" spreadsheet

Else
Sheets("Reports").Select
Range("A1:J35").Offset(35 * B, 0).EntireRow.Hidden = False 
     'Leave rows hidden or re-hide if unhidden

End If

Next B

End Sub

So what i'm trying to get it to do is to check the first cell in each box and if it has a red background then unhide specific rows in another sheet "Reports" that correspond to deficiency reports for that box. Each box represents a finished unit that is stored in my company's yard so it is important to have it unhide specific rows. All help is appreciated

 

[rcbunting]

I haven't tested this, but I notice something right away.

rng2 definition should be moved down into your For-Loop -- I believe you want it to be updated for each iteration, right?

modifications would look like:

Sub Butt*******83()
Dim rng1 As Range
Dim rng2 As Range
Dim B As Long
 
Set rng1 = Sheets("Layout").Range("C82:C87").Item(1, 1) 
     'first cell in the first box
 
For B = 0 To 17    '18 boxes
 
Set rng2 = rng1.Offset(0, 2 * B) 
'boxes occur every other column
 
If rng2.Interior.Color = RGB(255, 0, 0) Then                       
     'If cell has a red background
.
.
.

 

[Kinchen]

Yeah I want it to go to every other column and check to see if the first cell has a red background and if it does then it will unhide the rows in the "Reports" sheet that correspond to that specific box. Ie. Box 1 (C83:C87) on Sheet ("Layout") corresponds to Rows[1:35] on Sheet ("Reports"). I've noticed that it only looks at the first box and it doesn't loop through all the boxes (1-18) and it also seems to unhide all 18 box related rows instead of just the ones with the red backgrounds. What I want is if B=1 corresponds to a red backgound then the rows corresponding to B=1 unhide and then loop this for all B's. Any suggestions?

 

[Kinchen]

Hey thanks rcbunting. I've figured out the code I needed based on your and another persons help from another forum. It's been a crazy couple of days trying different variations and I forgot to follow the main rule by keeping it simple. Here is the code that works and it is surprisingly fast (1-2 seconds).

Sub Butt*******84()

Dim w As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer


For w = 1 To 54 'First 3 Rows of boxes stacked vertically (3X18)
    If Range("Range" & w).Item(1, 1).Interior.Color = RGB(255, 0, 0) Then
           'If the first cell in the range has a red background
       Sheets("Reports").[1:35].Offset((w - 1) * 35, 0).EntireRow.Hidden = False
           'Unhide rows that specifically relate to each box with a red background        
    Else
        Sheets("Reports").[1:35].Offset((w - 1) * 35, 0).EntireRow.Hidden = True
    End If
Next w

For x = 61 To 67 'Boxes on the top of the map stacked vertically
    If Range("Range" & x).Item(1, 1).Interior.Color = RGB(255, 0, 0) Then
       Sheets("Reports").[1:35].Offset((x - 1) * 35, 0).EntireRow.Hidden = False
        
    Else
        Sheets("Reports").[1:35].Offset((x - 1) * 35, 0).EntireRow.Hidden = True
    End If
Next x

For y = 55 To 60 'Boxes stacked horizontally
    If Range("Range" & y).Item(2, 3).Interior.Color = RGB(255, 0, 0) Then
       Sheets("Reports").[1:35].Offset((y - 1) * 35, 0).EntireRow.Hidden = False
        
    Else
        Sheets("Reports").[1:35].Offset((y - 1) * 35, 0).EntireRow.Hidden = True
    End If
Next y

For z = 68 To 75 'Last set of boxes stacked horizontally
    
    If Range("Range" & z).Item(2, 1).Interior.Color = RGB(255, 0, 0) Then
       Sheets("Reports").[1:35].Offset((z - 1) * 35, 0).EntireRow.Hidden = False
        
    Else
        Sheets("Reports").[1:35].Offset((z - 1) * 35, 0).EntireRow.Hidden = True
    End If
Next z

Sheets("Reports").Select
End Sub