Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
color hidden columns
Sorry for the newbie (in programming anyway) question.
I have found a great macro for colouring hidden rows from Andrew Engwirda which I have copied below. Could some kind programming guru help me transform it in to a macro to colour hidden columns. Thanks in advance, Barbara (from my spelling of colour, you may guess I am from the UK) http://blog.livedoor.jp/andrewe/archives/13105945.html Sub ColorHiddenRows() On Error GoTo Terminator Dim rw As Range Dim fstrw As Range Dim myRange As Range Dim myColor As Long Dim myPattern As Long Dim myPatternColor As Long Set myRange = Selection For Each rw In myRange.Rows If rw.Hidden = True Then If fstrw Is Nothing Then Set fstrw = rw Exit For End If End If Next fstrw.Select Application.Dialogs(xlDialogPatterns).Show myColor = ActiveCell.Interior.ColorIndex myPattern = ActiveCell.Interior.Pattern myPatternColor = ActiveCell.Interior.PatternColorIndex For Each rw In myRange.Rows If rw.Hidden = True Then rw.Interior.ColorIndex = myColor rw.Interior.Pattern = myPattern rw.Interior.PatternColorIndex = myPatternColor End If Next Exit Sub Terminator: MsgBox "There are no hidden rows ", vbExclamation End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
color hidden columns
One way:
Option Explicit Sub ColorHiddenCols() On Error GoTo Terminator Dim Col As Range Dim fstCol As Range Dim myRange As Range Dim myColor As Long Dim myPattern As Long Dim myPatternColor As Long Set myRange = Selection For Each Col In myRange.Columns If Col.Hidden = True Then If fstCol Is Nothing Then Set fstCol = Col Exit For End If End If Next fstCol.Select Application.Dialogs(xlDialogPatterns).Show myColor = ActiveCell.Interior.ColorIndex myPattern = ActiveCell.Interior.Pattern myPatternColor = ActiveCell.Interior.PatternColorIndex For Each Col In myRange.Columns If Col.Hidden = True Then Col.Interior.ColorIndex = myColor Col.Interior.Pattern = myPattern Col.Interior.PatternColorIndex = myPatternColor End If Next Exit Sub Terminator: MsgBox "There are no hidden columns ", vbExclamation End Sub Barbara Wiseman wrote: Sorry for the newbie (in programming anyway) question. I have found a great macro for colouring hidden rows from Andrew Engwirda which I have copied below. Could some kind programming guru help me transform it in to a macro to colour hidden columns. Thanks in advance, Barbara (from my spelling of colour, you may guess I am from the UK) http://blog.livedoor.jp/andrewe/archives/13105945.html Sub ColorHiddenRows() On Error GoTo Terminator Dim rw As Range Dim fstrw As Range Dim myRange As Range Dim myColor As Long Dim myPattern As Long Dim myPatternColor As Long Set myRange = Selection For Each rw In myRange.Rows If rw.Hidden = True Then If fstrw Is Nothing Then Set fstrw = rw Exit For End If End If Next fstrw.Select Application.Dialogs(xlDialogPatterns).Show myColor = ActiveCell.Interior.ColorIndex myPattern = ActiveCell.Interior.Pattern myPatternColor = ActiveCell.Interior.PatternColorIndex For Each rw In myRange.Rows If rw.Hidden = True Then rw.Interior.ColorIndex = myColor rw.Interior.Pattern = myPattern rw.Interior.PatternColorIndex = myPatternColor End If Next Exit Sub Terminator: MsgBox "There are no hidden rows ", vbExclamation End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
color hidden columns
Sub ColorHiddenColumns()
On Error GoTo Terminator Dim rw As Range Dim fstrw As Range Dim myRange As Range Dim myColor As Long Dim myPattern As Long Dim myPatternColor As Long Set myRange = Selection For Each rw In myRange.Columns If rw.Hidden = True Then If fstrw Is Nothing Then Set fstrw = rw Exit For End If End If Next fstrw.Select Application.Dialogs(xlDialogPatterns).Show myColor = ActiveCell.Interior.ColorIndex myPattern = ActiveCell.Interior.Pattern myPatternColor = ActiveCell.Interior.PatternColorIndex For Each rw In myRange.Columns If rw.Hidden = True Then rw.Interior.ColorIndex = myColor rw.Interior.Pattern = myPattern rw.Interior.PatternColorIndex = myPatternColor End If Next Exit Sub Terminator: MsgBox "There are no hidden rows ", vbExclamation End Sub -- Regards, Tom Ogilvy "Barbara Wiseman" wrote in message ... Sorry for the newbie (in programming anyway) question. I have found a great macro for colouring hidden rows from Andrew Engwirda which I have copied below. Could some kind programming guru help me transform it in to a macro to colour hidden columns. Thanks in advance, Barbara (from my spelling of colour, you may guess I am from the UK) http://blog.livedoor.jp/andrewe/archives/13105945.html Sub ColorHiddenRows() On Error GoTo Terminator Dim rw As Range Dim fstrw As Range Dim myRange As Range Dim myColor As Long Dim myPattern As Long Dim myPatternColor As Long Set myRange = Selection For Each rw In myRange.Rows If rw.Hidden = True Then If fstrw Is Nothing Then Set fstrw = rw Exit For End If End If Next fstrw.Select Application.Dialogs(xlDialogPatterns).Show myColor = ActiveCell.Interior.ColorIndex myPattern = ActiveCell.Interior.Pattern myPatternColor = ActiveCell.Interior.PatternColorIndex For Each rw In myRange.Rows If rw.Hidden = True Then rw.Interior.ColorIndex = myColor rw.Interior.Pattern = myPattern rw.Interior.PatternColorIndex = myPatternColor End If Next Exit Sub Terminator: MsgBox "There are no hidden rows ", vbExclamation End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
color hidden columns
hi,
change myRange.Rows in myRange.Columns. easy, isn't it? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
color hidden columns
Here you are Barabra, as requested, although I am at a loss as to know what
use it is to colour hidden columns? Also, be aware that there are a lot more rows than columns, so by colouring a whole column, you are in creasing the size of a workbook far more than by colouring a row. Sub ColourHiddenColumns() On Error GoTo Terminator Dim cw As Range Dim fstcw As Range Dim myRange As Range Dim myColor As Long Dim myPattern As Long Dim myPatternColor As Long For Each cw In ActiveSheet.UsedRange.Columns If cw.Hidden = True Then If fstcw Is Nothing Then Set fstcw = cw Exit For End If End If Next fstcw.Select Application.Dialogs(xlDialogPatterns).Show myColor = ActiveCell.Interior.ColorIndex myPattern = ActiveCell.Interior.Pattern myPatternColor = ActiveCell.Interior.PatternColorIndex For Each cw In ActiveSheet.UsedRange.Columns If cw.Hidden = True Then With cw.EntireColumn.Interior .ColorIndex = myColor .Pattern = myPattern .PatternColorIndex = myPatternColor End With End If Next Exit Sub Terminator: MsgBox "There are no hidden columns ", vbExclamation End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Barbara Wiseman" wrote in message ... Sorry for the newbie (in programming anyway) question. I have found a great macro for colouring hidden rows from Andrew Engwirda which I have copied below. Could some kind programming guru help me transform it in to a macro to colour hidden columns. Thanks in advance, Barbara (from my spelling of colour, you may guess I am from the UK) http://blog.livedoor.jp/andrewe/archives/13105945.html Sub ColorHiddenRows() On Error GoTo Terminator Dim rw As Range Dim fstrw As Range Dim myRange As Range Dim myColor As Long Dim myPattern As Long Dim myPatternColor As Long Set myRange = Selection For Each rw In myRange.Rows If rw.Hidden = True Then If fstrw Is Nothing Then Set fstrw = rw Exit For End If End If Next fstrw.Select Application.Dialogs(xlDialogPatterns).Show myColor = ActiveCell.Interior.ColorIndex myPattern = ActiveCell.Interior.Pattern myPatternColor = ActiveCell.Interior.PatternColorIndex For Each rw In myRange.Rows If rw.Hidden = True Then rw.Interior.ColorIndex = myColor rw.Interior.Pattern = myPattern rw.Interior.PatternColorIndex = myPatternColor End If Next Exit Sub Terminator: MsgBox "There are no hidden rows ", vbExclamation End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
color hidden columns
Tom,
Your solution worked on my spreadsheet, thank you so much. As to why I would want to colour hidden columns. We get spreadsheets from other people in the company, and instead of grouping columns or rows which they do not want to show, which is always my preference, they hide them. I sometimes need to see this information and then get the spreadsheet back to the state it was in to start with. I now realise that colouring the columns will inflate the spreadsheet size, and have thought of another method. This is to have a spare row at the top somewhere, highlight all the cells in that row on top of the data, F5, special, visible cells only (or use the toolbar icon) and colour these. When the columns are unhidden the unhidden columns are uncoloured on this row. Thanks to all who have given their time to help. Barbara Tom Ogilvy wrote: Sub ColorHiddenColumns() On Error GoTo Terminator Dim rw As Range Dim fstrw As Range Dim myRange As Range Dim myColor As Long Dim myPattern As Long Dim myPatternColor As Long Set myRange = Selection For Each rw In myRange.Columns If rw.Hidden = True Then If fstrw Is Nothing Then Set fstrw = rw Exit For End If End If Next fstrw.Select Application.Dialogs(xlDialogPatterns).Show myColor = ActiveCell.Interior.ColorIndex myPattern = ActiveCell.Interior.Pattern myPatternColor = ActiveCell.Interior.PatternColorIndex For Each rw In myRange.Columns If rw.Hidden = True Then rw.Interior.ColorIndex = myColor rw.Interior.Pattern = myPattern rw.Interior.PatternColorIndex = myPatternColor End If Next Exit Sub Terminator: MsgBox "There are no hidden rows ", vbExclamation End Sub "Barbara Wiseman" wrote in message ... Sorry for the newbie (in programming anyway) question. I have found a great macro for colouring hidden rows from Andrew Engwirda which I have copied below. Could some kind programming guru help me transform it in to a macro to colour hidden columns. Thanks in advance, Barbara (from my spelling of colour, you may guess I am from the UK) http://blog.livedoor.jp/andrewe/archives/13105945.html Sub ColorHiddenRows() On Error GoTo Terminator Dim rw As Range Dim fstrw As Range Dim myRange As Range Dim myColor As Long Dim myPattern As Long Dim myPatternColor As Long Set myRange = Selection For Each rw In myRange.Rows If rw.Hidden = True Then If fstrw Is Nothing Then Set fstrw = rw Exit For End If End If Next fstrw.Select Application.Dialogs(xlDialogPatterns).Show myColor = ActiveCell.Interior.ColorIndex myPattern = ActiveCell.Interior.Pattern myPatternColor = ActiveCell.Interior.PatternColorIndex For Each rw In myRange.Rows If rw.Hidden = True Then rw.Interior.ColorIndex = myColor rw.Interior.Pattern = myPattern rw.Interior.PatternColorIndex = myPatternColor End If Next Exit Sub Terminator: MsgBox "There are no hidden rows ", vbExclamation End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Worksheet, Hidden Columns, .CSV Format Saves Hidden Column Da | Excel Discussion (Misc queries) | |||
Copy and Paste with hidden columns remaining hidden | Excel Discussion (Misc queries) | |||
Hidden rows columns won't stay hidden | Excel Worksheet Functions | |||
Hidden Columns No Longer Hidden after Copying Worksheet? | Excel Discussion (Misc queries) | |||
How to keep hidden columns hidden using protection | Excel Discussion (Misc queries) |