Formatting Issue

**Isara-NJ** · 06-26-2017, 10:47 AM

Hi,

So I have a template that has a piece of formatting code which I obtained from a different post in this forum. It alternates gray & white on a copy of a pivot table to make it easier to read. Normally there are 5 different Sample Names per grouping but in this one example the user only had 3 so my formatting is off in the end. Would it be possible to get some help with this code to make it more dynamic. I would like that this modified piece of code can either get the number by input from the user or that it can get the number on its own (by maybe counting Vial rows or when there is a repeat in Sample Name) and create the correct formatting no matter how many the user may do? Please advise....

Thank you so much for your assistance.

**AlphaFrog** · 06-26-2017, 11:20 AM

Use this CF formula to alternate on\off for each change in column A

=COUNTA($A$5:$A5)/2=INT(COUNTA($A$5:$A5)/2)

**Isara-NJ** · 06-26-2017, 11:39 AM

I'm not sure I understand. Where do I put this code? Should it replace the formula I have there for calculating or add to it? Please advise....

**AlphaFrog** · 06-26-2017, 12:00 PM

On sheet RRT Pivoted Data, you already have conditional formatting setup to alternate color every 5th row using this CF formula
=MOD(ROW()-5,5*2)+1<=5
That conditional formatting is not part of your current macro, but it does copy over when you copy the data.

Change that formula to this formula to alternate color for each change in column A
=AND(COUNTA($A$5:$A5)/2=INT(COUNTA($A$5:$A5)/2), $B5<>"")

**Isara-NJ** · 06-26-2017, 12:45 PM

OK now I understand and wanted to say that that CF Formula gets put there by MyFormat piece of code, that runs along with others when the user clicks on the button on the Paste Rentention Data Tab, I never put it there. I have gone in to that CF formula and changed it to what you indicated and then applied it but it's still off. So my next question is if the user presses the button to run all the macros again will it over ride that formula or will it create another? And which CF will then take precedence? Here is the file again with the CF formula changed to what you told me.

**AlphaFrog** · 06-26-2017, 01:18 PM

This will clear previous CF formulas and add the new one.

Sub MyFormat()
    
    Dim rngMyRange As Range
    
    Set rngMyRange = Range("A5", Cells(Cells(Rows.Count, 2).End(xlUp).Row, Cells(4, Columns.Count).End(xlToLeft).Column))    'Set your range here
    rngMyRange.Select
    rngMyRange.FormatConditions.Delete
    With rngMyRange
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(COUNTA($A$5:$A5)/2=INT(COUNTA($A$5:$A5)/2), $B5<>"""")"
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 15
        End With
    End With
    
End Sub

**Isara-NJ** · 06-26-2017, 03:41 PM

Thank you soo much, that did it and it's working great!

Formatting Issue

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View