Excel PivotTable Conditional Formatting

Question

I have a pivot table in an Excel worksheet that contains the result of a query made to my database. I would like to format the information automatically based on every other data set.

The information contains 4 weeks' (1 month) worth of records for each employee sorted by an employee ID number. I would like to write a module so that it will highlight every other record (employee data set) with a different color. Is this even possible to do? Thanks for the help!

enter image description here

hello, I don't believe you need VBA for this. You could just go to Home -> Styles -> Format as Table -> Select a format that highlights every other row. Of course, this also depends on how your pivot table is grouping data. If you could provide a screenshot it would help out a lot. — Joseph, Oct 18 '12 at 12:57
Ok I uploaded the screen shot of what I want it to look like (done manually, with 100,000 records, its just not time effiecient) — designspeaks, Oct 18 '12 at 14:04
thanks for taking the time to make the image! The only reason I recommend doing a style is because you may want to change your column arrangement someday and you may be able to avoid changing any formulas/vba code with styles. However, styles are a bit of a pain to work with. I'll see if I can come up with something useful. Otherwise, I would recommend a conditional format formula like an answer below. — Joseph, Oct 18 '12 at 14:44

score 1 · Accepted Answer · answered Oct 18 '12 at 13:08

1

If you insist with solving your problem utilizing VBA here is an example. You'll need to specify start ranges. Please not that marking whole row will use more memory (increasing file size) so I would rather use example: range("A2:E2).select ....

Sub FormatEverySecondRow()
         range("A2").EntireRow.Select
         Do While ActiveCell.value <> ""
             Selection.Interior.ColorIndex = 15
             ActiveCell.offset(2, 0).EntireRow.Select
         Loop
End Sub

answered Oct 18 '12 at 13:08

salih0vicX

1,363
1
8
9

1

You can try also the following: `Sub FormatEverySecondRow() Dim i As Long Dim bShade As Boolean bShade = True i = 3 Do While Range("A" & i).Value <> "" If Range("C" & i).Value <> "" Then If bShade = True Then bShade = False Else bShade = True End If End If If bShade = True Then Range("B" & i & ":H" & i).Interior.ColorIndex = 15 Else Range("B" & i & ":H" & i).Interior.ColorIndex = 14 End If i = i + 1 Loop End Sub ` – salih0vicX Oct 18 '12 at 18:56

score 0 · Answer 2 · answered Oct 18 '12 at 14:40

0

use a helper column (K if I count the columns in your example)

insert into K2:

=IF(ISBlank(C2),K1,MOD(K1+1,2))

then use conditional formatting to highlight the row:

Conditional Formatting

Note the formula does not have a $ sign before the 2 (i.e. $K2, not $K$2)

answered Oct 18 '12 at 14:40

SeanC

15,695
5
45
66

score 0 · Answer 3 · answered Oct 18 '12 at 15:33

This might be useful to you:

Sub HighlightDifferentRows()

Dim wksht As Worksheet
Dim wkb As Workbook
Dim row As Range
Dim FloatColor As Long

FloatColor = RGB(100, 100, 100)

Set wbk = ThisWorkbook

Application.ScreenUpdating = False

For Each row In Sheets(1).UsedRange.Rows
    row.Interior.Color = FloatColor
    If row.Cells(1, 4).Value <> row.Cells(2, 4).Value Then
        FloatColor = -FloatColor
    End If
Next row

Application.ScreenUpdating = True

End Sub

AlternateColorsAfterMacro

It alternates row colors whenever a cell value is not the same as the one below it. Right now it is set to grayish colors but you could change it to something brighter if you wanted. You could put in your own logic to get whatever colors you wanted. Good Luck.

Excel PivotTable Conditional Formatting

3 Answers3