how to shade a row of cells if 1 cell is odd or even

[pecoflyer]

Hi
I am trying to shade a row of cells if the last cell is odd or even.
I have created the formula to give me an odd or even number in a hidden cell. I can use this and conditional formatting to shade that row ( shade = odd, leave = even ) BUT.. this information is then copied to 2 other spreadsheets but because of the conditional formatting the shade does not copy ( even if I use paste special - format ) here is an example.
I have used the 5th and 6th numbers in the first number (consignment) to create my odd or even number which sits in column L (hidden).

21100101 997 WED01 04:30
21100202 995 WED02 05:00
21100201 993 WED02 05:00
21100302 991 WED03 05:30
21100301 989 WED03 05:30
21100401 987 WED04 07:15
21100503 985 WED05 08:00
21100502 983 WED05 08:00
21100501 981 WED05 08:00

I already have a macro in use to merge the WED** column, Is there a way to add the shade to this macro ??

Thank you in advance...

Gary.


The merge macro I am using.........

Code: Select all

Sub merge()
    col = "J"
    lr = Cells(Rows.Count, col).End(xlUp).Row
    fr = 1
    tr = 1
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Do Until fr >= lr
        Do While Cells(tr + 1, col) = Cells(fr, col)
            tr = tr + 1
        Loop
        Range(Cells(fr, col), Cells(tr, col)).merge
        fr = tr + 1
        tr = fr
    Loop
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

[NoSparks]

Merged cells are a cosmetic curse on Excel and should be avoided like the plague.

Try applying conditional formatting with a macro, something along the lines of this

Code: Select all

'range for conditional formatting
Set rng = Range("A5:S" & lastrow)
With rng
    'remove existing CF
    .Cells.FormatConditions.Delete
    'add conditional formatting
    .FormatConditions.Add Type:=xlExpression, Formula1:="=your formula in quotes"
    With .FormatConditions(1)
        .Interior.ColorIndex = 14   'grey
        .StopIfTrue = False
    End With
End With


[pecoflyer]

@GaryH
Hi and welcome. In the future please wrap code with code tags. Thx