Setting Interior Color Index for all False rows

[yevlar]

This is probably a simple command I'm overlooking. I'm working on a worksheet to track progress of orders. It's a fairly simple set-up. In one row is the name of the ordered item, a drop-down list detailing where the item is located, and check boxes (all Forms-based) to track the progress of the item. The last check box (ActiveX control) is in a column (starting from G3) marked "Completed." When this is checked, the entire row background switches to red.

At the top of the worksheet is a Master Checkbox (Also ActiveX control) marked "Hide Completed." This box is set up to hide all red rows in the worksheet.

Here's the current code:

Private Sub CheckBox1_Click()
Dim Blah As Range
Set Blah = Range("z3")
If Blah.Value = True Then
Blah.EntireRow.Interior.ColorIndex = 3
Else
Blah.EntireRow.Interior.ColorIndex = 2
End If

End Sub

Private Sub Master_Click()
Dim Blah2 As Range
Set Blah2 = Range("a3:a1000")
If Master.Value = True Then
    For Each cl In Blah2
        If cl.Interior.ColorIndex = 3 Then
        cl.EntireRow.Hidden = True
        End If
    Next cl
Else
    For Each cl In Blah2
        If cl.Interior.ColorIndex = 3 Then
        cl.EntireRow.Hidden = False
        End If
    Next cl
End If
If Master.Value = True Then
CheckBox1.Visible = flase
Else
CheckBox1.Visible = True
End If

End Sub

So, Checkbox1 is in Cell G3, Linked to cell Z3 (just to get the true/false text out of the viewable area), and when Z3 returns the falue of "True" (when Checkbox 1 is checked), the color index for the row background is set to Red.

The Master Checkbox looks to range Blah2 to seek out red rows and hide them. The Master Checkbox when checked also hides Checkbox1.

This works great. However, with this code I would need to alter the code for each row individually (like, for Row 4, Checkbox2 will need to be manually linked to Cell Z4, and then Checkbox2 would also need to be manually set to visible.false when Master is checked.)

This worksheet will contain hundreds upon hundreds of rows, each of which need their own control to turn the background to red AND to have the Completed checkbox turn invisible when the Master is hiding all Completed rows. As I've said before, I'm still very much an infant when it comes to VBA (this is the first code I've written without asking for direct help), so I'm unsure what commands should be used to accomplish this.

Is there a VBA control that works similarly to the INDIRECT command that will adjust itself to reflect its current row via copy/paste? Or is there a regular Forms control I'm overlooking that will work this way? (Am I overthinking this?)

 

[Von Pookie]

Just linking to your old thread for reference:
http://www.mrexcel.com/board2/viewtopic.php?t=233586

 

[yevlar]

Any ideas on what else I could use to accomplish this? Any help would be greatly appreciated. Thanks!

 

[yevlar]

In reference to previous thread

Kristy - sorry I didn't see your post to the previous thread (my e-mail's been down for a few days.)

To answer your question - In the macro you wrote there is no highlighted line in the VB code when Error 400 occurs. I'm still very much a beginner at this, but I can't see anythin in the code that would be cause for error.

 

[yevlar]

One thing I notice in the codes you wrote - the first set - sub hide() - gets Error 400 when both checking and unchecking the master checkbox. The second set - sub hide2() - only gets Error 400 when hiding (master checkbox checked.)

 

[Dufus]

yevlar,

Just my opinion but, I think you're making this too complex. I wouldn't use a check box to indicate completion if I were you. I'd use Data Validation with a simple choice of "1" equals complete and "0" equals not complete. Then you wouldn't need to worry about how to set it up for large quantities of rows. And, the macro would only have to search for 1s to hide. I guess you could color the rows red before you hide them, but why bother if they're just going to be hidden. Now that I think about it, why use a macro for that anyway? Why not use Auto Filter from the menu and when you want to see only the ones with "0"s, just select "0" from the drop down list.

Dufus

 

[yevlar]

I was hoping to make this as idiot-proof as possible,because I'm dealing with people who have no idea how to use Excel. Plus, we currently use check boxes for all of these orders - except on physical paper and dry-erase boards. I figured check-boxes would be the best way to convert everyone to a paperless workflow.

Plus - now that I've come this far, I don't want to just give up. I'm on a mission, now!

 

[yevlar]

If I WERE to use, say, an X instead of a check box in the Complete column (F3:F1000 in this case), what would be the command for seeking out all instances of that X in the range and switching those row background colors? I tried this, but it didn't work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Blah As Range
Set Blah = Range("f3:f1000")
If ActiveSheet.Blah = X Then
ActiveSheet.Blah.EntireRow.Interior.ColorIndex = 3
Else
ActiveSheet.Blah.EntireRow.Interior.ColorIndex = 2
End If


End Sub

 

[Von Pookie]

If I WERE to use, say, an X instead of a check box in the Complete column (F3:F1000 in this case), what would be the command for seeking out all instances of that X in the range and switching those row background colors?

For starters, I don't think you want to use a SelectionChange event for this. I can't see how it would be effective in this situation.

If you're going to go with 'X' instead of checkboxes--meaning the cells will either have a value or no data in them whatsoever, you could use SpecialCells.

For example:

Dim Rng As Range
Set Rng = Range("F3:F1000")

With Rng
    'cells within the range that have any value (not just those with an 'X') will be colored red
    .SpecialCells(xlCellTypeConstants, 23).EntireRow.Interior.ColorIndex = 3
    
    'blank cells within the range will not be colored
    .SpecialCells(xlCellTypeBlanks).EntireRow.Interior.ColorIndex = xlNone
End With

If you're wanting to do it automatically, for something like this a simple Change event should work perfectly--not a SelectionChange

 

[yevlar]

Works fabulously!

Thank you all for all of your help. In the future I may try to revisit the Checkbox thing (after I learn a little more about what the heck I'm doing) but this will certainly get us started.

THANKS!!!!!!