1004- Unable to set the hidden property of the range class

[Darin Kramer]

Hi there,

I have a macro that looks at columns in a workbook, and if the colour in
line 189 matches the colour of the cell when the macro is run it leaves
the column visible. If the colour is different it hides the column.

It works perfectly up until about 5 minutes ago, when I get the subject
title error - It also works from column F until column GW - and then
gets stuck. Im no expert... so appreciate any advice.
The Macro that runs the colour hiding / not is shown below

Any ideas...??

Apprecaite the help....!!!

Kind Regards

D


Public Sub Colour_views()

Dim objWS As Excel.Worksheet
Dim objCell As Excel.Range, objR As Excel.Range
Dim i As Byte
Dim J As Long

Set objWS = Sheets("Master")
' Don't forget that wherever you're looking at, the selected cell needs
to have a background
' color that matches the various "views" you've built on the master
sheet.

Set objCell = Application.Selection

J = objCell.Interior.ColorIndex

' Hide columns
' If columns A - F are always going to be there, we only need to start
with Column E, which
' is column number 6.
For i = 6 To 223

Set objR = objWS.Cells(2, i) ' We'll always be looking at row 2 -
the column will change
If objR.Interior.ColorIndex <> J Then

objWS.Columns(i).Hidden = True

End If

Next

Set objR = Nothing
Set objCell = Nothing
Set objWS = Nothing

Sheets("master").Select
End Sub

 

[Don Guillett]

try this simple one withOUT selections

Sub hidecolsbycolor()
For i = 6 To 223
With Sheets("master")
If .Cells(2, i).Interior.ColorIndex <> _
.Cells(189, i).Interior.ColorIndex Then _
.Columns(i).Hidden = True
End With
Next i
End Sub

 

[Darin Kramer]

Not sure if we progressing...It doesnt get stuck... but produces the
wrong result ie it just matches colours in row 2 with colours in row
180. I need it to match colour of cell selected when macro run with
colours visible in row 180...

Regards

d

 

[Don Guillett]

I tested this. When any cell on row 2 does NOT match the color of the cell
on row 189 the column will become hidden. I understood this to be what you
wanted. You said 189 and now you say 180? Please FULLY explain your desires.