CODEWITHSUNDEEP
excelvbscriptvba
CodeLover
CodeLover

Reputation: 1074

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

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

Upvotes: 4

Views: 27476

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

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.

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

Upvotes: 8

Richard
Richard

Reputation: 61289

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)

Upvotes: 2

Related Questions