Solved: Simple Formatting

[lukecj]

Quick question. I am writing a macro to format the attached excel worksheet. I am trying to adjust this code:

[VBA]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[/VBA]

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

[IBihy]

Hello Charles,

one way I see is to cycle through the rows, and keep track of the cells with value "Block" in them. If that count is divisible by 2 without remainder, then color .... uh-oh, this may be tricky, because one block consist of 1 to n lines... let me see what I can do with it.

Have a nice day, and a little patience please,
Isabella

[lukecj]

Thanks! Plenty of patience here. Let me know if you need anymore detail.

[Bob Phillips]

You could use conditional formatting with a formula of

=MOD(COUNTIF(INDEX($A$4:OFFSET($A$1,MIN(IF($A5:$A$1000="Block",ROW($A5:$A$1 000)))-2,0),0,1),"Block"),2)=1

[Bob Phillips]

Here is some VBA if you must be boring

[vba]

Public Sub ProcessData()
Dim NumBlocks As Long
Dim Lastrow As Long
Dim i As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 4 To Lastrow

If .Cells(i, "A").Value = "Block" Then

NumBlocks = NumBlocks + 1
End If
If NumBlocks Mod 2 = 1 Then

.Rows(i).Interior.ColorIndex = 24
End If
Next i
End With

End Sub
[/vba]

[lukecj]

Thanks. I'll try it both ways. Never even thought of trying to tackle it with conditional formatting.

[GTO]

[vba]If .Cells(i, "A").Value = "Block" Then

NumBlocks = NumBlocks + 1
End If
If NumBlocks Mod 2 = 1 Then

.Rows(i).Interior.ColorIndex = 24
End If[/vba]

Damn, that is just slick!

[lukecj]

In the VBA, is there any way to stop the highlighting at Column T instead of the whole row?

[lukecj]

Curious about NumBlocks...what is that actually representing/counting?

[Bob Phillips]

In the VBA, is there any way to stop the highlighting at Column T instead of the whole row?

In the conditional formatting, just select the rows from column A to T.

In the VBA, change

[vba]

If NumBlocks Mod 2 = 1 Then

.Rows(i).Interior.ColorIndex = 24
End If
[/vba]

to

[vba]

If NumBlocks Mod 2 = 1 Then

.Cells(i, "A").Resize(, 20)Interior.ColorIndex = 24
End If
[/vba]

[Bob Phillips]

Curious about NumBlocks...what is that actually representing/counting?

It counts the number of blocks of rows, rather than just the number of rows. It increments every time it hits a value of Block in column A.

[lukecj]

Thanks so much. This helps a lot. I appreciate the explanations.

[mdmackillop]

For the code in Post #5, this change will restrict the formatting to the desired columns
[VBA] With ActiveSheet.Columns("A:T")
[/VBA]

[Bob Phillips]

For the code in Post #5, this change will restrict the formatting to the desired columns
[VBA] With ActiveSheet.Columns("A:T")
[/VBA]

What a nice idea!