Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Shading For Visible Rows Only
I am using conditional formatting formula =MOD(Row(),2)=0 to shade
alternate rows, but when I hide a row the shading format is not consistent. Is there way to shade alternate 'Visible Only' rows without creating a macro? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Shading For Visible Rows Only
you can do it using a user-defined-function. The UDF is then used on the worksheet. Would that class as a macro for this purpose? regards -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=543113 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Shading For Visible Rows Only
The UDF seems a little too complex for me. Is there maybe some kind of
'Visible' code that I could use for the conditional formatting formula? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Shading For Visible Rows Only
this function returns a true or false depending on the number of visible rows Function visLines(ByRef rng As Range) As Boolean Application.Volatile Dim rng1 As Range Dim rng2 As Range Dim bln As Boolean Set rng1 = rng.Resize(rng.Row).Offset(1 - 1 * rng.Row) For Each rng2 In rng1 If rng2.Height < 0 Then bln = Not bln End If Next Debug.Print rng1.Address, l, bln visLines = bln End Function then use conditional formatting using the cell reference so if in cell D5 use vilines(D5) cheers -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=543113 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
CAN I HIDE ROWS WITH CONDITIONAL FORMATTING? | Excel Discussion (Misc queries) |