Geek Answers Handbook

I can not clear and reset the cell background color

Using the command below, I can 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 following:

 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 this be done on one line?

thanks

+4
source share
2 answers

Everything is good. But don’t choose if you are using a huge script (knowing that you have passed so far) ...

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

If you use range , you can even remove the with block, since it also has some disadvantage in slowing down performance.

Answer the following questions:

  • How to get column name from column number?

    Excel column number from column name

  • How to set a range based on the name or OP number of maxcolumn .

    Range (row, column) .

    You mentioned that you need row 1 , maxcolumn , then you can build a cell using these two data.

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

    try:

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

+8
source

You can 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 which one (I don't have Excel). If you could try both and report, that would be great.

You can set colors using:

 Selection.Interior.Color = RGB(255,0,0)
+2
source

More articles:


All Articles