Please fix my macro quickly!

[ClimoC]

Hello

I have a range of data which I want colour-coded if the date in a cell is different to the one above it.

If it is the same as the date above it, I want the colour to be the same as that cell.

The below macro almost works - it scans the range, and if the cell above it is different to its date, the cell changes colour. However, the next cell beneath that (which might happen to share the same date) doesn't change.

Also if possible I'd like to be colouring all rows but only in the array G6:L500 and not the columns outside this array.

here is my code

Sub colourme()
Dim r As Range
Dim c As Range
Dim rw As Range

Set r = Range("Schedule!G6:G500")
Set rw = r.Rows

        For Each rw In r
           
            If rw.Offset(-1, 1).Value = rw.Offset(0, 1).Value Then
            rw.Interior.ColorIndex = rw.Offset(-1, 1).Interior.ColorIndex
            ElseIf rw.Offset(-1, 1).Value <> rw.Offset(0, 1).Value Then
            rw.Interior.ColorIndex = 7
            End If
        Next
        
End Sub

ANy help would be MUCH appreciated.
Cheers

 

[ravishankar]

Hi
try these codes

sub climo()
Dim a As Long, c As Long
c = 2
    For a = 2 To Range("G65536").End(xlUp).Row
    Range(Cells(a, 7), Cells(a, 12)).Interior.ColorIndex = c
        If Cells(a, 7) <> Cells(a + 1, 7) Then
        c = c + 1
        End If
    Next a
MsgBox "complete"
End Sub

ravi

 

[yytsunamiyy]

I would try the following:

Use a helper column - in this example column "B" - to check if a row is the same as the row(s) below / above:

Excel Workbook
	A	B
2	1.1.2009	1
3	1.2.2009	2
4	1.1.2009	3
5	1.3.2009	4
6	1.1.2009	5
7	1.1.2009	5
8	2.1.2009	6
9	2.1.2009	6
10	2.1.2009	6
11	2.1.2009	6
12	3.1.2009	7
13	3.1.2009	7
14	3.1.2009	7
15	3.1.2009	7
16	3.1.2009	7
Tabelle1

in B2 copied down:

=IF(AND(A2=A1,A2=A3),B1,IF(A2=A1,B1,B1+1))

Then run the following macro:

Sub foo()
Dim cll As Range
For Each cll In Sheets(1).Range(Cells(2, "B"), Cells(16, "B"))
cll.EntireRow.Interior.ColorIndex = cll.Value + 1
Next cll
End Sub

The result:

Excel Workbook
	A	B
2	1.1.2009	1
3	1.2.2009	2
4	1.1.2009	3
5	1.3.2009	4
6	1.1.2009	5
7	1.1.2009	5
8	2.1.2009	6
9	2.1.2009	6
10	2.1.2009	6
11	2.1.2009	6
12	3.1.2009	7
13	3.1.2009	7
14	3.1.2009	7
15	3.1.2009	7
16	3.1.2009	7
Tabelle1

This gives you an individual color for each new block of dates.