Thread: Many columns
View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
MDW MDW is offline
external usenet poster
 
Posts: 117
Default Many columns

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 ***