Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting - break on Chg in Seq (Col A)
In Range A1:A10 I have the values:
1 2 3 4 1 << break in seq (is less or the same as previous row) 2 3 1 << break in seq (is less or the same as previous row) 1 << break in seq (is less or the same as previous row) 2 Columns B:L down to Row 10 have various other data I would like a Macro that will perform conditional Formatting in a toggle-fashion (green/blue) breaking on a sequence change in Col A; Note Rows 1:4 = Green Rows 5:7 = Blue Row 8 = Green Rows 9:10 = Blue Can someone suggest a start? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting - break on Chg in Seq (Col A)
Hi Jim,
Try the below code. Sub CondFormat() 'This macro assumes that the change condition is space in column A 'You can change it to your condition in the IF statement below Dim i As Integer Dim j As Integer Dim mLastRow As Integer j = 1 'Find the last row which has data in column A mLastRow = Range("A65000").End(xlUp).Row 'Start from row 1 until last row For i = 1 To mLastRow + 1 If Range("A" & i).Value = "" Then 'If the change in pattern found Range("B" & j & ":L" & i - 1).Select Selection.Interior.ColorIndex = 6 j = i + 1 End If Next 'Move the selction to cell A1 Range("A1").Select End Sub HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "Jim May" wrote: In Range A1:A10 I have the values: 1 2 3 4 1 << break in seq (is less or the same as previous row) 2 3 1 << break in seq (is less or the same as previous row) 1 << break in seq (is less or the same as previous row) 2 Columns B:L down to Row 10 have various other data I would like a Macro that will perform conditional Formatting in a toggle-fashion (green/blue) breaking on a sequence change in Col A; Note Rows 1:4 = Green Rows 5:7 = Blue Row 8 = Green Rows 9:10 = Blue Can someone suggest a start? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting - break on Chg in Seq (Col A)
Format all the cells in green, then use conditional formatting to override
that setting Select all the data cells in the second row, then use Format-Conditional formatting and choose Formula is and use this formula =$a2<=$a1 Now click on the format button, go to the patterns tab and choose the blue color you want. OK your way back to the worksheet, then paint the format from this row down to all the other rows "Jim May" wrote: In Range A1:A10 I have the values: 1 2 3 4 1 << break in seq (is less or the same as previous row) 2 3 1 << break in seq (is less or the same as previous row) 1 << break in seq (is less or the same as previous row) 2 Columns B:L down to Row 10 have various other data I would like a Macro that will perform conditional Formatting in a toggle-fashion (green/blue) breaking on a sequence change in Col A; Note Rows 1:4 = Green Rows 5:7 = Blue Row 8 = Green Rows 9:10 = Blue Can someone suggest a start? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting - break on Chg in Seq (Col A)
Pranav:
After running macro - My sheet ends up with the Range(B1:L10) fully yellow in color with Column A unformatted -- not the desired results. Thanks, Jim "Pranav Vaidya" wrote: Hi Jim, Try the below code. Sub CondFormat() 'This macro assumes that the change condition is space in column A 'You can change it to your condition in the IF statement below Dim i As Integer Dim j As Integer Dim mLastRow As Integer j = 1 'Find the last row which has data in column A mLastRow = Range("A65000").End(xlUp).Row 'Start from row 1 until last row For i = 1 To mLastRow + 1 If Range("A" & i).Value = "" Then 'If the change in pattern found Range("B" & j & ":L" & i - 1).Select Selection.Interior.ColorIndex = 6 j = i + 1 End If Next 'Move the selction to cell A1 Range("A1").Select End Sub HTH, -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "Jim May" wrote: In Range A1:A10 I have the values: 1 2 3 4 1 << break in seq (is less or the same as previous row) 2 3 1 << break in seq (is less or the same as previous row) 1 << break in seq (is less or the same as previous row) 2 Columns B:L down to Row 10 have various other data I would like a Macro that will perform conditional Formatting in a toggle-fashion (green/blue) breaking on a sequence change in Col A; Note Rows 1:4 = Green Rows 5:7 = Blue Row 8 = Green Rows 9:10 = Blue Can someone suggest a start? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting - break on Chg in Seq (Col A)
Duke:
Thanks, but this only changes the First-record of the break-in-sequence. I was wanting to continue with the same blue color for the remaining rows in the new sequence - note Rows 5:7 = Blue -- not just Row 5 alone. Note Rows 1:4 = Green Rows 5:7 = Blue Row 8 = Green Rows 9:10 = Blue Thanks, Jim "Duke Carey" wrote: Format all the cells in green, then use conditional formatting to override that setting Select all the data cells in the second row, then use Format-Conditional formatting and choose Formula is and use this formula =$a2<=$a1 Now click on the format button, go to the patterns tab and choose the blue color you want. OK your way back to the worksheet, then paint the format from this row down to all the other rows "Jim May" wrote: In Range A1:A10 I have the values: 1 2 3 4 1 << break in seq (is less or the same as previous row) 2 3 1 << break in seq (is less or the same as previous row) 1 << break in seq (is less or the same as previous row) 2 Columns B:L down to Row 10 have various other data I would like a Macro that will perform conditional Formatting in a toggle-fashion (green/blue) breaking on a sequence change in Col A; Note Rows 1:4 = Green Rows 5:7 = Blue Row 8 = Green Rows 9:10 = Blue Can someone suggest a start? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - Before Page Break | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
How break column formatting to vary cell widths vertically? | Excel Worksheet Functions | |||
Page break formatting changes when sent via e-mail | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |