VBA / Conditional Formatting?

[TheThird152]

Hi gang!

I have a spreadsheet that I create on a daily basis. It has five different VBA defined ranges that must stay separate. For each range, the last column (Column L in all cases) contains a combined first and last name (Adam Adamson, for instance). These names are not always the same, and there are different numbers of rows each day. Adam Adamson will appear a random number of times each day (once one day, three times the next day, not at all the following day, then twice the next day, and so on).

My ranges already sort to alphabetical by column L. What I need to have happen is some sort of conditional format that fills every OTHER value in column L (and the corresponding cells in the same row) with the same color (yellow in this instance).

I've included a link to an image as an example with two days worth of fictional data (work has very strict guidelines concerning what I can post online, so excuse the ridiculousness of the names) already formatted to what I want the end result to be. The actual lists I deal with are much, much longer and I've been color formatting by hand.

Thanks in advance for any help (and sorry if there is something that is unclear).

THANKS!

Paul

Picture link: http://www5.snapfish.com/snapfish/s...otsc=SHR/otsi=SPIClink/COBRAND_NAME=snapfish/

 

[cpod]

Could you add another column? Put this formula in M2 and copy down:

=IF(L2=L1,M1,M1+1)

Then the conditional formatting formula would be:

=IF(MOD(M2,2)=0,TRUE,FALSE)

 

[TheThird152]

The additional column actually works wonderfully! Thanks so much for the tip.

Now, a question about how to insert the CF code into my VBA code:

Here is the code for the first range that I'm using (I used the macro recorder then tweaked a little, so there may be erroneous code in there):

Range("M2").Select
ActiveCell.Formula = "=IF(L2=L1,M1,M1+1)"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M97"), Type:=xlFillDefault
Range("M2:M97").Select

It works great, but now how do I implement the CF formula? Thanks so much for your help, I swear I'm getting better at this :P

Paul

 

[cpod]

Dim ws As Worksheet, rng As Range
Set ws = ThisWorkbook.Worksheets(1)
ws.Range("m2").Formula = "=IF(L2=L1,M1,M1+1)"
ws.Range("M2:" & "M" & ws.Range("l10000").End(xlUp).Row).FillDown
Set rng = ws.Range("L2:L" & ws.Range("l10000").End(xlUp).Row)
rng.Select
rng.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(MOD(M2,2)=0,TRUE,FALSE)"
rng.FormatConditions(1).Interior.Color = 65535

 

[lenze]

Here's another way

Sub AddStuff()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
With ws.Range("M2:M" & LR)
      .Formula = "=IF(L2=L1,M1,M1+1)"
End With
For Each cl In Range("M2:M" & LR)
    If cl Mod 2 = 0 Then
       Cells(cl.Row, "A").Resize(1, 13).Interior.ColorIndex = 6
    Else: Cells(cl.Row, "A").Resize(1, 13).Interior.ColorIndex = xlNone
    End If
Next cl
End Sub

Actually, you could probably do this without Column "M"
lenze