CODEWITHSUNDEEP
excelvbamodulepivot-table
designspeaks
designspeaks

Reputation: 203

Excel PivotTable Conditional Formatting

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

Upvotes: 2

Views: 2728

Answers (3)

Stepan1010
Stepan1010

Reputation: 3136

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.

Upvotes: 0

SeanC
SeanC

Reputation: 15923

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)

Upvotes: 0

salih0vicX
salih0vicX

Reputation: 1373

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

Upvotes: 1

Related Questions