Export to Excel module-Need formatting help please-Coloring Cells 1

[MyaCCt]

Hi all,

Kind of a newbie here... hoping to get some info on how to write code in order to have the report that I'm having generated in Access, exported to Excel through a module, have colors based on what is in the cell (specifically date information.)
For the first column to have the color grey across the top bar, I have used: xlSheet.Range("A1", "V1").Interior.ColorIndex = numCOLOR_Grey
Now what I want to do is if a date appears that is in the past, to have that colored red. If it's coming up in the next 30 days, I want that colored yellow. The sticky bit is though, that the date field is a text field and some have letters after the date. Example: the date could either look like 3/14/2014 or 3/14/2014 (p).

Am I making any sense at all? Please let me know if you need more information in order to assist.

Thank you!
-MCC

 

[PHV]

I'd try this:

or r = 1 To 220
    For c = 1 To 11    'A to K
        Select Case xlSheet.Cells(r, c).Value
        Case "", ", ,"
            xlSheet.Cells(r, c).Interior.ColorIndex = numCOLOR_Light_Orange
        End Select
    Next c
Next r

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MyaCCt]

For r = 1 To 220
For c = 1 To 8 'A to H
If xlSheet.Cells(r, c).Value = "" Then xlSheet.Cells(r, c).Interior.ColorIndex = numCOLOR_Light_Orange
Next c
Next r

For r = 1 To 220
For c = 1 To 8 'A to H
If xlSheet.Cells(r, c).Value = ", ," Then xlSheet.Cells(r, c).Interior.ColorIndex = numCOLOR_Light_Orange
Next c
Next r

For r = 1 To 220
For c = 1 To 8 'A to H
If xlSheet.Cells(r, c).Value = " " Then xlSheet.Cells(r, c).Interior.ColorIndex = numCOLOR_Light_Orange
Next c
Next r



Seems to be working well! Only thing is... I have some sheets on the excel that are less than 220 lines long. Is there a way to get it so it stops at the last line and doesnt color in, say, lines 25-220?
Thank you, thank you, thank you!
-MCC

 

[Andrzejek]

Look at PHV's code and add to the Case " " (one space)

Hint - every time you need to repeat the same code, there is usually a better way to do it. And by ‘better’ I mean simpler, more efficient, easier to read, easier to maintain, more robust, etc.)

And since in Row 1 you have the header row, see my post from 15 May 14 9:29

“get it so it stops at the last line” you need to define what the ‘last line’ is to your program. For example, is there a data in any column that you can loop thru (go down the rows in that column), so if you hit an empty cell, that’s the ‘last line’ of the data.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MyaCCt]

Oh yes, that is simpler. Thank you!
There is always going to be a value in column A. If there is a blank down the row, the one before it is the last line.

Thanks!

 

[Andrzejek]

If that's the case, consider using this instead:

r = 2

Do While xlSheet.Cells(r, [blue]1[/blue]).Value <> "" 
    ...
    r = r + 1
Loop

Just check if there is a value in a cell in column A down the rows.
As soon as you hit an empty cell, you are done.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MyaCCt]

It gave me an error saying it expected a "to" after r = 2. Weird?
Thanks!

 

[Andrzejek]

I would guess you have in your code:
[tt]
For r = 2[/tt]

Where you just need
[tt]
r = 2[/tt]

I may be wrong since I don't really know what code you have...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MyaCCt]

Now it says that I'm missing a "For" (which I had in the for r= 2 to 220 previously)

r = 2
Do While xlSheet.Cells(r, 1).Value <> ""
r = r + 1
Loop

Select Case xlSheet.Cells(r, c).Value
Case "", ", ,", " "
xlSheet.Cells(r, c).Interior.ColorIndex = numCOLOR_Light_Orange
End Select
Next c
Next r


I must be doing something out of order.
Thanks!

 

[SkipVought]

Why are you assigning a value to r, when you have r in a [highlight #FCE94F]For r...Next r[/highlight] loop along with incriminating r in a [highlight #FCE94F]Do...Loop[/highlight]???

Please post ALL your code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Since I don't know what you code looks like now, I can only go by what you showed us the last time. So the change would be:

[blue]
r = 2[/blue]
[s]For r = 1 To 219[/s][blue]
Do While xlSheet.Cells(r, 1).Value <> ""[/blue]
    For c = 12 To 22 'L to V
        If xlSheet.Cells(r, c).Value <> "" Then
            strDate = Split(xlSheet.Cells(r, c).Value, " ")(0)
            ary = Split(xlSheet.Cells(r, c).Value, " ")
            If UBound(ary) > 0 Then
                If IsDate(strDate) Then
                    Select Case CDate(strDate)
                        Case Is < Date
                    xlSheet.Cells(r, c).Interior.Color = numCOLOR_Red
                        Case Is < Date + 30
                    xlSheet.Cells(r, c).Interior.Color = numCOLOR_Light_Blue
                        Case Is > Date + 30
                    xlSheet.Cells(r, c).Interior.Color = numCOLOR_Yellow
                    End Select
                End If
            End If
        End If
    Next c[blue]
    r = r + 1
Loop[/blue]
[s]Next r[/s]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.