Using Conditional formatting with subtotals

xcelguru

New Member
Joined
Nov 21, 2011
Messages
2
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
Joined
Aug 4, 2002
Messages
851
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
Joined
Nov 21, 2011
Messages
2
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
Joined
Aug 4, 2002
Messages
851
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
Joined
Aug 4, 2002
Messages
851
ignore this now blank duplicate post!!
 
Last edited:

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top