lukecj
02-15-2011, 09:47 AM
Quick question. I am writing a macro to format the attached excel worksheet. I am trying to adjust this code:
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "Block" Then
Set del = cell
Range(del, del.End(xlDown).Offset(-1, 0)).Select
Range(Selection, Selection.Offset(0, 19)).Select
With Selection.Interior
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
End With
End If
Next cell
On Error Resume Next
Instead of highlighting every instance the color indicated, I want to highlight every other instance of "block" (which has differing ranges - see Rows 8-14 and compare to Rows 4-5) starting at the second instance of "block" (Rows 6-7). Also, instead of highlighting the remaining area in the entire worksheet, is there a way to have it stop at the last instance? The first tab is what needs to be modified and the second tab is what I am trying to make it look like using this macro.
Please let me know if I need to clarify and thanks very much for the help. If there is an entirely better way to do this than simply modifying the included code, I would love to see it. Thanks.
Charles
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "Block" Then
Set del = cell
Range(del, del.End(xlDown).Offset(-1, 0)).Select
Range(Selection, Selection.Offset(0, 19)).Select
With Selection.Interior
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
End With
End If
Next cell
On Error Resume Next
Instead of highlighting every instance the color indicated, I want to highlight every other instance of "block" (which has differing ranges - see Rows 8-14 and compare to Rows 4-5) starting at the second instance of "block" (Rows 6-7). Also, instead of highlighting the remaining area in the entire worksheet, is there a way to have it stop at the last instance? The first tab is what needs to be modified and the second tab is what I am trying to make it look like using this macro.
Please let me know if I need to clarify and thanks very much for the help. If there is an entirely better way to do this than simply modifying the included code, I would love to see it. Thanks.
Charles