PDA

View Full Version : change color of cells from Access



talytech
12-27-2012, 08:35 AM
HI,

I'm trying to change the font color for a range of cells on my Excel worksheet from MS Access.

Basically, I want to change the font color to black for cells A3:B28

Here's what I have :

Dim xlapp As Excel.Application
Dim wb As Excel.workbook
Dim ws As Excel.worksheet
Dim rs As Recordset
Dim mfile As String
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
Dim myRangeA, myRangeB As Range

mfile = "H:\myfolder\CasesSubmitted_autoFormat.xls"

Set xlapp = New Excel.Application
xlapp.Visible = False
xlapp.DisplayAlerts = False
Set wb = xlapp.Workbooks.Open(mfile, , , , , , , , , -1)

Set myRangeA = wb.Worksheets("sheet1").Range(wb.Worksheets("sheet1").Range("A3"), wb.Worksheets("sheet1").Range("A3").End(xlDown))
myRange.Cells.Font.Color = RGB(0, 0, 0)
myRange.Cells.Font.Bold = False

This code works for cells A3 to the last cell with data in column A.

I want to do the formatting for a range to the last row with data in that specified range.. Does that make sense??

How do I do the exact same thing for range A3:B28 without specifying the last row?

Aflatoon
12-28-2012, 07:29 AM
If there are no completely blank rows or columns in the range, replace
Set myRangeA = wb.Worksheets("sheet1").Range(wb.Worksheets("sheet1").Range("A3"), wb.Worksheets("sheet1").Range("A3").End(xlDown))
myRange.Cells.Font.Color = RGB(0, 0, 0)
myRange.Cells.Font.Bold = False
with
With wb.Worksheets("sheet1")
With .Range("A3", .Usedrange.specialcells(xlcelltypelastcell)).Font
.Color = RGB(0, 0, 0)
.Bold = False
end with
end with