# Using Conditional formatting with subtotals

#### xcelguru

##### New Member
I am trying to work out a spreadsheet that I am using conditional formatting to highlight every two rows. I have 2 lines for each item and i need them to be grouped together. I can get the formula =mod(row(),4)<2
to do this, but then when i go to add the subtotals for each section it screws up the lines that are highlighted. For instance.. I have 2 associates on this spreadsheet and i use the stubtotal function to see how many each of them did, But when having the every two lines highlighted with matching information, and then adding the subtotals, it screws up the matching highlighted lines.. Is there anyway to fix it?

#### BigC

##### Well-known Member
Without subtotals applied, doesn't your mod formula need to be [=mod(row(),4)< =2]? (Ignore the space after the less than sign)

When you apply subtotals, do you have just one item set per associate followed by a single subtotal line, or more than one item set followed by a single subtotal line??

#### xcelguru

##### New Member
in my case, I have 2 associates, One has 10 rows, 5 sets of 2 information lines. the 2nd associate has 200 rows with 100 sets of information. I can use the conditional formatting to highlight the 2 rows, then skip two rows and so on.. But Once I put in the subtotals in, (which i will need because on other sheets, there is potential for more than 1 or 2 associates,) It messes up the sets being highlighted. the only thing I have seem to found that wont mess up the highlighting is adding a blank line inbetween the subtotal lines, but then it messes up on the filtering.. I guess I might be just trying to do too much on one sheet!

#### BigC

##### Well-known Member
You need to get away from row numbers and use a count of items by associate (irrespective of which rows these are in)

Assuming:
Row 1 contains the column headings
Column B contains your associate ID

I think this may do, or is close to, what you're after:
=MOD(COUNTIF(B\$1:B1,B2),4)<2

#### BigC

##### Well-known Member
ignore this now blank duplicate post!!

Last edited:

1,081,748
Messages
5,361,051
Members
400,610
Latest member
ebey

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...