OK, if you want everything to be in row 2, then we can change a few things.
Note the comments I added within the code itself.
Public Sub DoTemplate()
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.
Perhaps
' have a chart, where users click a cell that has a "view" name, and that
cell has a
' background color corresponding to the cells on the master sheet.
Set objCell = Application.Selection
J = objCell.Interior.ColorIndex
' Hide columns
' If columns A - D are always going to be there, we only need to start with
column E, which
' is column number 5.
For I = 5 To 52
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
End Sub
--
Hmm...they have the Internet on COMPUTERS now!
"Darin Kramer" wrote:
hmm...Im almost there, (but as u can tell am no programmer!), could I
have a bit more assistance.... in telling macro only to look at row 2's
background colour (i dont want to highlight all the backround colours of
the whole column....)
What i have so far is:
Public Sub DoTemplate()
Dim objWS As Excel.Worksheet
Dim objCell As Excel.Range, objR As Excel.Range
Dim I As Byte, K As Byte
Dim J As Long
Set objWS = Sheets("master")
Set objCell = Application.Selection
K = objCell.Row
J = objCell.Interior.ColorIndex
' Hide rows
'For I = 2 To 2
' If I < K Then
' objWS.Rows(I).Hidden = True
' End If
'Next
' Hide columns
For I = 2 To 52
Set objR = objWS.Cells(K, I)
If objR.Interior.ColorIndex < J Then
objWS.Columns(I).Hidden = True
End If
*** Sent via Developersdex http://www.developersdex.com ***