I have a pivot table that is aproximately 100 rows by 52 columns. The column
headings are dates, and the row headings also contain dates. I am trying to
write a macro that does 2 things.
1. Verify if there is data in the cell.
2. If there is data, and the data at the top of the column is greater than
the date of the row, I want to fill the cell in red.

I have tried:
For r=1 to 100
For c = 1 to 52
IF Activecell.value.isblank=true then Next r
Elseif Activecell.value (R, C[-c]) Activecell.value(R[-r], C) Then
With Selection.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With

Next R
Next C

I am having trouble with the syntax of the if statements. Any assitance
would be greatly appreciated.

JC

Dim r as Long, c as Long, cell as Range
For r=2 to 101
For c = 2 to 53
set cell = cells(r,c)
IF cell.value<"" then
if cells(r,1) cells(1,c) Then
With cell.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
end if
Next C
Next R

--
Regards,
Tom Ogilvy


"JC" wrote:

I have a pivot table that is aproximately 100 rows by 52 columns. The column
headings are dates, and the row headings also contain dates. I am trying to
write a macro that does 2 things.
1. Verify if there is data in the cell.
2. If there is data, and the data at the top of the column is greater than
the date of the row, I want to fill the cell in red.

I have tried:
For r=1 to 100
For c = 1 to 52
IF Activecell.value.isblank=true then Next r
Elseif Activecell.value (R, C[-c]) Activecell.value(R[-r], C) Then
With Selection.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With

Next R
Next C

I am having trouble with the syntax of the if statements. Any assitance
would be greatly appreciated.

JC

I have copied the below code to visual basic editor and added the opening and
closing information. When I try to step through the code I get a compile
error: Next without For. I can see both of them in the code, I am very
confused, does something else need to be added?

"Tom Ogilvy" wrote:

Dim r as Long, c as Long, cell as Range
For r=2 to 101
For c = 2 to 53
set cell = cells(r,c)
IF cell.value<"" then
if cells(r,1) cells(1,c) Then
With cell.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
end if
Next C
Next R

--
Regards,
Tom Ogilvy


"JC" wrote:

I have a pivot table that is aproximately 100 rows by 52 columns. The column
headings are dates, and the row headings also contain dates. I am trying to
write a macro that does 2 things.
1. Verify if there is data in the cell.
2. If there is data, and the data at the top of the column is greater than
the date of the row, I want to fill the cell in red.

I have tried:
For r=1 to 100
For c = 1 to 52
IF Activecell.value.isblank=true then Next r
Elseif Activecell.value (R, C[-c]) Activecell.value(R[-r], C) Then
With Selection.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With

Next R
Next C

I am having trouble with the syntax of the if statements. Any assitance
would be greatly appreciated.

JC

The code I provided was just a guess at your word picture. You might have
to play with it a little to actually match the data you have.


Dim r as Long, c as Long, cell as Range
For r=2 to 101
For c = 2 to 53
set cell = cells(r,c)
IF cell.value < "" then
if cells(r,1) cells(1,c) Then
With cell.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
end if
End if
Next C
Next R

--
Regards,
Tom Ogilvy


"JC" wrote in message
...
I have copied the below code to visual basic editor and added the opening
and
closing information. When I try to step through the code I get a compile
error: Next without For. I can see both of them in the code, I am very
confused, does something else need to be added?

"Tom Ogilvy" wrote:

Dim r as Long, c as Long, cell as Range
For r=2 to 101
For c = 2 to 53
set cell = cells(r,c)
IF cell.value<"" then
if cells(r,1) cells(1,c) Then
With cell.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
end if
Next C
Next R

--
Regards,
Tom Ogilvy


"JC" wrote:

I have a pivot table that is aproximately 100 rows by 52 columns. The
column
headings are dates, and the row headings also contain dates. I am
trying to
write a macro that does 2 things.
1. Verify if there is data in the cell.
2. If there is data, and the data at the top of the column is greater
than
the date of the row, I want to fill the cell in red.

I have tried:
For r=1 to 100
For c = 1 to 52
IF Activecell.value.isblank=true then Next r
Elseif Activecell.value (R, C[-c]) Activecell.value(R[-r], C) Then
With Selection.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With

Next R
Next C

I am having trouble with the syntax of the if statements. Any
assitance
would be greatly appreciated.

JC