Hide columns according to background fill color

[Craig Remillard]

I am having trouble understanding how Excel handles colors.

I have a public sub that sets a public variable, "TermColor" using the RGB function. TermColor is of type MsoRGBType.

In another module, I use the TermColor variable as follows:

Sub WeedColsByColor(ByRef Clr, ByRef WS)

Dim LastCol, i As Long

With Worksheets(WS)

LastCol = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

'hide columns if they have one of the forbidden colors
Debug.Print (CBool(.Cells(2, i).Interior.Color = Clr))
For i = 1 To LastCol
If .Cells(2, i).Interior.Color = Clr Then
.Cells(2, 1).EntireColumn.Hidden = True
End If '.Cells(2, i).Interior.Color = Color Then
Next i
End With 'Worksheets(WS)
End Sub

The debug statement gives me an error: Run-time error '1004': Application-defined or object-defined error

When I take the debug statement out and call the sub, I get nothing - no error, but also no columns are hidden. Using another debug statement, I am able to see that the IF statement is never entered.

Does anyone know either of the following:
1. How to phrase the IF statement so it compares cell color to Clr, or
2. How to output the .color and Clr to a hex or separate RGB decimal values?


Submitted via EggHeadCafe - Software Developer Portal of Choice
XML into Data Islands Direct from SQL Server 2000
http://www.eggheadcafe.com/tutorial...b-bd916321c72a/xml-into-data-islands-dir.aspx

 

[Craig Remillard]

....or how to convert the RGB "Clr" value to a ColorIndex value using the workbook's default palette?



Craig Remillard wrote:

Hide columns according to background fill color
10-Jan-10

I am having trouble understanding how Excel handles colors.

I have a public sub that sets a public variable, "TermColor" using the RGB function. TermColor is of type MsoRGBType.

In another module, I use the TermColor variable as follows:

Sub WeedColsByColor(ByRef Clr, ByRef WS)

Dim LastCol, i As Long

With Worksheets(WS)

LastCol = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

'hide columns if they have one of the forbidden colors
Debug.Print (CBool(.Cells(2, i).Interior.Color = Clr))
For i = 1 To LastCol
If .Cells(2, i).Interior.Color = Clr Then
.Cells(2, 1).EntireColumn.Hidden = True
End If '.Cells(2, i).Interior.Color = Color Then
Next i
End With 'Worksheets(WS)
End Sub

The debug statement gives me an error: Run-time error '1004': Application-defined or object-defined error

When I take the debug statement out and call the sub, I get nothing - no error, but also no columns are hidden. Using another debug statement, I am able to see that the IF statement is never entered.

Does anyone know either of the following:
1. How to phrase the IF statement so it compares cell color to Clr, or
2. How to output the .color and Clr to a hex or separate RGB decimal values?

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Content Director Test Article
http://www.eggheadcafe.com/tutorial...f-e92ac7a4593f/content-director-test-art.aspx

 

[Dave Peterson]

Without any testing at all...

LastCol = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
should probably be using .column and .columns.count