4

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

Community
  • 1
  • 1
CodeLover
  • 1,054
  • 6
  • 24
  • 40
  • @bonCodigo any short way to do the same? – CodeLover Jan 02 '13 at 12:36
  • Excel **does not** use VBScript. Do you want a VBScript or a VBA solution? – Fionnuala Jan 02 '13 at 13:13
  • @Remou I am using VBScript to work with Excel. I need VBScript approach.But one thing is to mention here that VBA code can be converted to VBScript with little bit change. Thus I used `VBScript` and `EXCEL VBA` tagging. – CodeLover Jan 02 '13 at 13:24
  • In that case your tags are VBScript and Excel, not Excel-VBA, you are looking for a VBScript solution, not a VBA solution. It is as well to say that VB6 can easily enough be converted to VBA and VBScript -- where does it end? – Fionnuala Jan 02 '13 at 13:41

2 Answers2

8

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

Community
  • 1
  • 1
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • any idea how to select in VBA the range say column 9 to onwards for say row#1? – CodeLover Jan 02 '13 at 10:37
  • use `offset, resize` like given in the yesterday's post ;) `'-- `highlight the max total row.` `With Sheets(3).Range("B3").Offset(maxRowNum - 1, 0).Resize(1, UBound(arrInput, 1) + 1)` `.Interior.Color = 200` `End With` – bonCodigo Jan 02 '13 at 10:41
  • Yes I got your point but, I have no idea about what the value of `maxcolumn` is? So `.Range("B3")` what should I write? and I want to for row#1 ,from `maxcolumn` to the last column of excel should set as `no colors` – CodeLover Jan 02 '13 at 10:47
  • 1
    IT'S FROM THE YESTERDAYS CODE TUKAI. SO we assume you have gone through it already. I have not used `maxcolumn` anyway. Try to put some values and see how it works, so you will understand these two properties. And your above code in the questions is already selecting row one from whatever `startcolumn to maxcolumn`. So WHY DO YOU SAY YOU DON'T KNOW YOUR OWN CODE THAT YOU POSTED? – bonCodigo Jan 02 '13 at 10:50
  • You misunderstood my confusion. See you put `"B3"` in the `Sheets(3).Range("B3")` in my case ` maxcolumn` is the one which I have to put over there. But I don't how to get the Column name from the column number. That's the query I did to you! – CodeLover Jan 02 '13 at 10:53
  • 1
    If you need to colour the Cell B3 onwards the whole row. Do this, `.Range("B3").offset(0,0).Resize(1,maxcolumn).Interior.Color = 100` – bonCodigo Jan 02 '13 at 10:54
  • Ohh. the way you put forward it very confusing :$ I am updating the answer for your this second question (how to get the column name from column number and set the range for whole row starting from `maxcolumn`). Check the update please. – bonCodigo Jan 02 '13 at 10:55
  • Yeah My code is clearing contents only not the background color, So now I think or changed my plan I will clear the color for the columns which are no use. and the column number starts from any number to the alst excel column number which I want to set as `No color`! – CodeLover Jan 02 '13 at 10:55
2

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)
Richard
  • 56,349
  • 34
  • 180
  • 251