MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I change the background color of a particular cell?


Posted by Anjali on November 28, 2000 12:23 PM

Hi,

How do I change the backcolor of a particular cell?

TIA,
Anjali


Posted by Ben O. on November 28, 2000 12:40 PM

If it's not already visible, bring up your drawing toolbar by selecting View > Toolbars > Drawing.

There's a button on the drawing toolbar called Fill Color. Select the cell(s) you want to color and click on the small arrow to the right Fill Color button. Choose the color you want, then press the button itself.

Another way to do it is to highlight the cells and select Format > Cells, select the Patterns Tab, choose the color you want, and press OK.

-Ben

Posted by Anjali on November 28, 2000 12:54 PM

Hi Ben,

Thanks for the information. But I want to change the
backcolor of a cell programaically...

Suppose I need to change the backcolor of a cell:
cells(2,3)
How do I do that thru code?

Could you please help me out.

TIA,
Anjali

Posted by Ben O. on November 28, 2000 1:07 PM

Ah, I should have known that your question warranted a more complex answer than the one I gave.

To just change the color of cell(2,3), you could use this:

Range("B3").Interior.ColorIndex = 6

6 is yellow. If you want a different color, you'll have to change the number. I'm not of the numbers that correspond to each color. You'll have to experiment.

If you want to let the user select the cells that will be colored, I recommend created a userform that consists of a RefEdit box and an OK button. Call up the userform in your code, have the cells that the user specifies selected when the OK button in pressed, and then change the color of the selection with:

Selection.Interior.ColorIndex = 6

I hope this helps,

-Ben

Posted by Anjali on November 29, 2000 12:24 AM

Hi Ben,

I need to change the backcolor of random rows and
cols.

Suppose there are two variables:

Rowcount - For selecting particular row.
ColCount - For selecting particular column.

How I set the 'Range' with these variables?

Then I can use the statement as:

Selection.interior.colorindex=6

Could you please help me.

TIA,
Anjali

I proceeded Ah, I should have known that your question warranted a more complex answer than the one I gave. To just change the color of cell(2,3), you could use this: Range("B3").Interior.ColorIndex = 6 6 is yellow. If you want a different color, you'll have to change the number. I'm not of the numbers that correspond to each color. You'll have to experiment. If you want to let the user select the cells that will be colored, I recommend created a userform that consists of a RefEdit box and an OK button. Call up the userform in your code, have the cells that the user specifies selected when the OK button in pressed, and then change the color of the selection with: Selection.Interior.ColorIndex = 6 I hope this helps,

Posted by Ben O. on November 29, 2000 6:52 AM

Okay, try this:

Range(Cells(ColCount, RowCount), Cells(ColCount, RowCount)).Interior.ColorIndex = 6

If you assign positive integers to ColCount and RowCount, it should work.

-Ben

Hi Ben, I need to change the backcolor of random rows and

Posted by Anjali on November 29, 2000 11:13 AM

Hi Ben!

Thanks a lot for your help. I shall surely come to you
if have any more doubts.

Actually I had protected the sheet and was
attempting to do the changes. Just for that
simple reason I struggled the whole day yesterday.

Anyway I could fix the problem with your sincere
Help.

Thanks once again.
Anjali Okay, try this: Range(Cells(ColCount, RowCount), Cells(ColCount, RowCount)).Interior.ColorIndex = 6 If you assign positive integers to ColCount and RowCount, it should work.

Posted by Celia on December 03, 2000 4:19 PM

Re: Color Index


Just as an aside, the index number for a particular colour can be obtained either by using the macro recorder or as explained in Mr Excel's Quick Answers at http://www.mrexcel.com/qa.shtml :-

Q. Can I see a list of the ColorIndex and the corresponding color?
A. To see the colors, go to the visual basic editor. Hit the help questionmark. Type ColorIndex. From the list of topics, select the one called "ColorIndex Property". They have a nice visual table at the bottom of this help topic.
You could also build a color table with a quick macro

Public Sub ColorTable()
For i = 1 To 56
Range("A" & i).Interior.ColorIndex = i
Range("B" & i).Value = i
Next i
End Sub

Celia