If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Automatically color merged cells
Using Excel '07, I'm trying to automatically highlight a list of vertically
merged cells the same color as their unmerged rows next to them. For example: Column A might have cells A2-A5 merged, while cells B2-B5 are not. I'd like the entire row(s) all the same color, and the next merged set a different color (A6-A7 merged, with B2-B7 not). Is this possible to do automatically? Automatic table formatting will unmerge cells in column A and alternately color each individual row. I would like the groups of rows to each be colored differently. Thanks |
#2
|
|||
|
|||
Automatically color merged cells
You mean like banding--red, yellow, red, yellow, red, yellow????
If yes, then you could use something like: Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim myColor As Long Set wks = Worksheets("Sheet1") With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row iRow = FirstRow myColor = 3 Do If iRow LastRow Then Exit Do End If .Cells(iRow, "A").MergeArea.EntireRow.Interior.ColorIndex = myColor iRow = iRow + .Cells(iRow, "A").MergeArea.Rows.Count If myColor = 3 Then myColor = 6 Else myColor = 3 End If Loop End With End Sub If you don't like those colors, record a macro when you change the fill to colors that you do like and substitute them in the code (4 places!). Rick wrote: Using Excel '07, I'm trying to automatically highlight a list of vertically merged cells the same color as their unmerged rows next to them. For example: Column A might have cells A2-A5 merged, while cells B2-B5 are not. I'd like the entire row(s) all the same color, and the next merged set a different color (A6-A7 merged, with B2-B7 not). Is this possible to do automatically? Automatic table formatting will unmerge cells in column A and alternately color each individual row. I would like the groups of rows to each be colored differently. Thanks -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|