Conditional formatting of data exported to Excel 3

[MeldrethMan]

I'm successfully sending data to Excel, code extract below

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblSelectedClients")
rst.MoveFirst
Do While Not rst.EOF
objSht.Cells(iRow, 1).Value = rst!Name
objSht.Cells(iRow, 2).Value = rst!Company
objSht.Cells(iRow, 3).Value = rst!Address1
objSht.Cells(iRow, 4).Value = rst!Address2
objSht.Cells(iRow, 5).Value = rst!Address3
objSht.Cells(iRow, 6).Value = rst!Town
objSht.Cells(iRow, 7).Value = rst!County
objSht.Cells(iRow, 8).Value = rst!Postcode
objSht.Cells(iRow, 9).Value = rst!FirstName
objSht.Cells(iRow, 10).Value = rst!LastName
objSht.Cells(iRow, 11).Value = rst!Status
iRow = iRow + 1
rst.MoveNext
Loop

I'd like to have conditional formatting in the resulting spreadsheet according to the value of Status. This can have several values so would like say

If Status is Invoiced, colour the cell Red
If Paid, then Green

Going a step further, colouring the entire spreadsheet row would be a real bonus.

Any steers would be much appreciated.

 

[MeldrethMan]

Have found out how to do part of this for the Status cell, by adding

If rst!Status = "Paid" then
objSht.Cells(iRow,11).Interior.Color=RGB(200,100,35)
etc for other Status values

Is there an easier way of applying the colour to the whole Excel row than having to repeat the formatting for (iRow,1) to (iRow,10)?

 

[PHV]

I'd try this:
objSht.Cells(iRow,11)[!].EntireRow[/!].Interior.Color=RGB(200,100,35)

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

 

[TheAceMan1]

How are ya MeldrethMan . . .

Be aware ... the method you've shown is not [blue]Conditional Formatting[/blue]. To perform this with no code perform the following (I'm using 2003):
[ol][li]Select the range of cells you know will be involved. Remember the [blue]TopRow[/blue] in your selection.[/li]
[li]On the menubar select [blue]Format[/blue] - [blue]Conditional Formatting[/blue]. The [blue]Conditional Formatting Dialog[/blue] opens.[/li]
[li]In the far left combobox select [blue]Formula Is[/blue].[/li]
[li]Put the cursor in the textbox just to the right. Now ... assuming the [blue]TopRow[/blue] in your selection is [blue]1[/blue], enter [purple]$K1="Paid"[/purple][/li]
[li]Select the [blue]Format...[/blue] button, make your selections and hit [blue]OK[/blue] when your done.[/li]
[li]Select the [blue]Add >>[/blue] button and perform steps 3 thru 5 for [blue]Invoiced[/blue] ... [purple]$K1="Invoiced"[/purple].[/li]
[li]Hit [blue]OK[/blue] for final checkout.[/li]
[li][blue]Thats It! ...[/blue][/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MeldrethMan]

Thanks to you both, know I'm in safe hands with you guys.

PHV - Your suggestion
objSht.Cells(iRow,11).EntireRow.Interior.Color=RGB(200,100,35)
gave 'Application-defined or Object-defined error' but
objSht.Cells.EntireRow.Interior.Color=RGB(200,100,35)
colours the entire worksheet. I'll look for way of making it specific to the current row.

Ace - If I understand your suggestion it's to apply the formatting once the data arrives in Excel, which is fine, thanks.

 

[TheAceMan1]

[blue] ... it's to apply the formatting once the data arrives in Excel, which is fine, thanks.[/blue]

Yes. [blue]Conditional Formatting[/blue] will pickup any cell changes in column [blue]K[/blue] and format proper.



See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Gammachaser]

Conditional formatting can be applied through code. If you are going to let others use the spreadsheets and want them to stay within certain parameters, you can code the conditional formatting in to let them know when they exceed the limits. It is a little obscure at first, but it once you get the syntax, it is not hard to do.

If you are just making a 'static' spreadsheet where the users can't affect what makes the cells a certain color (i.e. in your case no one will cause the status to change by manipulating the spreadsheet) what you are doing works well.