Export to Excel and format

[aytee111]

I have exported to Excel and formatted successfully. However, I am trying to find certain rows (with a criteria) and higlight just those rows in the spreadsheet. When I attempted it my computer got itself all out of whack and it took me ages to sort it all out, so I am afraid to try anything else! Can anyone help with the code to filter on just the applicable rows in order to change their color?



Thanks

[rpeare]

I don't follow your question. Are you exporting your data to excel, then while you're in excel, trying to find a specific record(s) but you want those rows that meet your criteria to be highlighted somehow?

Or are you exporting data to excel and you want the format of the entire file to be altered?

[Monterey_Manzer]

I concur with rpeare on not quite following what you are trying to describe. Some specifics would likely be helpful. However, I did just implement something similar to what I think you are getting at. I will show you my code below

I have a report that gets exported to Excel for distribution. One of the columns in the report is High Priority which has either TRUE or FALSE values. The following code shows how I find the right column and highlight those rows

Dim lRow As Integer, lCol As Integer, i As Integer, HPriCol As Integer

    lRow = xlWs.Range("A3").End(xlDown).Row
    lCol = xlWs.Range("A3").End(xlToRight).Column 
    HPriCol = xlWs.Range("A3:" & A(lCol) & "3").Find("High Priority", , xlValues).Column

For i = 4 To lRow
        If xlWs.Range(A(HPriCol) & i).value = "TRUE" Then
            With xlWs.Range("A" & i & ":" & A(lCol) & i).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 65535
            End With
        End If
Next i

I hope that helps get you started.

[aytee111]

Sorry for not being very clear in my original post. Monterey_Manza seems to have given me some code which sounds like it is doing what I want. Yes, to find all the rows which have a certain value, such as True or False, and highlight only those rows. I will give it a try tomorrow. Thanks for responding.

[aytee111]

Fantastic!! This worked perfectly. I was fortunate enough to know which column held the value so didn't have to go looking for it, however.

I am truly grateful for your help.

[Monterey_Manzer]

Glad it worked out for you. I don't know how often your reports change but I use the .Find method of the Excel range because the users of my dBase regularly request tweaks to either the formatting or data presented in some of the reports it generates. By using the Find method, I don't have to re-write my formatting code every time I add or subtract a column. Maybe you are lucky enough to never have this problem but it might prove helpful. Also, the more you rely on the code to find the right column, the more widely the code can be applied to other reports. Just a thought for the future.

[aytee111]

That's a good warning - although I am an experienced Access programmer, this is the first time that I have had to do any of this Excel formatting stuff. That is definitely something to program into the code, the users adding just one column would require rewrite.

Hmmmm....

[Monterey_Manzer]

I'm still a relative newbie to Access but exporting to Excel and formatting has be a requirement for everything I build since I got started. I realize I forgot one thing to make the whole "Find" feature work. I had problems using the .Cells property of a Excel.Range object in conjunction with the column number so I decided to simply write a function that would give me a matrix of the alphabet. I know there should be another way but this just made my life much more simple. It goes out to "AZ" for those really long spreadsheets.

Function Alpha()
Dim i As Integer, A(52) As String

    For i = 1 To 26
        A(i) = Chr(i + 64)
    Next i
    For i = 27 To 52
        A(i) = Chr(65) & Chr(i + 38)
    Next i
    Alpha = A
End Function

I usually just add a variant (usually A) in my code and use A = Alpha. Then, whenever the find method for a column returns an integer, I just plug the value it to the resulting array. That's where the A(HiPriCol) and A(lCol) come in for the code I originally posted.