I am not able to clear and reset the background color of a Cell

Question

With the help of the below command I am able to clear the contents of the cells but not their background color. How to clear and set the background color of cells in a range?

ob9.Range(ob9.Cells(1,StartCol),ob9.Cells(1,maxcolumn)).ClearContents

EDIT

I tried the below :

CountFill = objExcel1.Application.WorksheetFunction.CountA(ob9.Rows(1))
CountBlnk = objExcel1.Application.WorksheetFunction.CountBlank(ob9.Rows(1))
TotalColumn= CountBlnk + CountFill

ob9.Range(ob9.Cells(1,CountFill + 1 ),ob9.Cells(1,TotalColumn)).Interior.ColorIndex(-4142) '= xlColorIndexNone

Can it be done in a single line?

Thanks

Answer 1

You could try

ob9.Range(ob9.Cells(1,StartCol),ob9.Cells(1,maxcolumn)).Select
Selection.Interior.ColorIndex = xlColorIndexNone
Selection.Interior.ColorIndex = xlNone

One of the last two lines should work, but I'm not sure off-handedly which one (I don't have Excel). If you could try both and report back, that would be great.

You can set colors using:

Selection.Interior.Color = RGB(255,0,0)

Answer 2

Everything is fine. But don't select given you are running a huge script (knowing what you went through so far)...

with ob9.Range(ob9.Cells(1,StartCol),ob9.Cells(1,maxcolumn))
.Interior.ColorIndex = xlColorIndexNone
.Interior.ColorIndex = 120
End With

If you are directly using the range you may even remove with block, as it too has some performance-slowing drawback.

• Reference article

Answer for your sub questions:

1. How to get column name from column number?

   Excel column number from column name

2. How to set range based on the OP's maxcolumn name or number.

   Range(row,column).

   You mentioned you need row 1, maxcolumn then you can build the cell using those two data.

   MsgBox Sheets(3).Rows(1).Columns(5).Address

   so try out:

   MsgBox Sheets(3).Rows(1).Columns(maxcolumn).Address