Help Please

[Guest]

Hi All,
I have developed an application using Visual Basic & Access. My query is i
am getting the report from access database to excel while exporting data from
access to excel i want all the cell with NO to be filled with red color this
can be done by conditional formatting but i want this to be automated as soon
this is extracted from access database.

TIA

 

[Bob Phillips]

You can add the CF in the code that gets the data

Dim rng As Range


Set rng = Range("A:B") '<=== change to suit
rng.FormatConditions.Delete
rng.FormatConditions.Add Type:=xlExpression, Formula1:="=RC=""Yes"""
rng.FormatConditions(1).Interior.ColorIndex = 3
Set rng = Nothing


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RonaldvM]

I am assuming you Export from Access and thus your macro is running from acces. So first you have to open an instance from excel. After this you have control over excel like you would from excel itself. You can then alter the color of cells etc.


try someting like this. Good luck.

Sub WriteToExcel()
'don't forget to register your reference to Excel Oject Library!
Dim objXLS As Object
Dim myfile As String
Dim row As Integer
	myfile = "C:\Temp\TEST.XLS"
	Set objXLS = CreateObject("excel.application")
	objXLS.Application.Visible = True
	objXLS.workbooks.Open (myfile)				  'open your excel file
	objXLS.worksheets(1).Select
	For row = 1 To 100
	If Cells(row, 1) = "No" Then  'look for cell with text "No"
		objXLS.Cells(row, 1).Interior.ColorIndex = 6	 'color the cell
	End If
	Next row
	objXLS.ActiveWorkbook.Close SaveChanges:=True   'save excel file
	objXLS.Quit									 'quit excel
	Set objXLS = Nothing
End Sub