Free Microsoft Excel 2013 Quick Reference

Transparency for interior color?

Hi all,

I would like to ask you about, how can I do(if it's possible), that the interior color will be transparent where it's in connection with the gridlines?? (Like when I select some cells)

So I want, that the gridlines don't disappeare when I get an interior color for the cells.


Post your answer or comment

comments powered by Disqus

I recorded a macro for setting the interior color of a cell to the lightest
gray in the color palet on the toolbar. But I need a lighter shade of gray.
I experimented with various color indexes, but these indexes seem to only use
the colors from the color palet on the toolbar. Is there a way to use the
RGB color function maybe, to color the interior of a cell to a shade that is
not on the color palet on the toolbar? How is this done?


Hey guys,

I am using the attached spreadsheet that I made to determine what colors apply to what values for interior index colors.

As you can see there is a list down the side and each cell was colored based on it's value.

The problem I have now is when I try and apply a color like 44 it displays a color like 12

This is being used in Excel 2003. Are these colors not supported in this version ??

This is the Excel 2003 Format Cell Panel .... the first image is from XL2003 on my work PC, the second one is XL2003 on my laptop.
But that being said, when I open the attached file on the work PC I see the colors as the full range available.

Note the variation in colors across the grid, most notably down the second column from the left and across the bottom.

Hello, i'd like to use a macro to loop through a range of cells and change the interior color index based on the cell values (ex. if cell value < 10, set interior to blue, if cell value > 400, set to green) i'm trying a for each loop with an if statement but i keep getting syntax errors, i know this is probably a simple problem but i'm not experienced in vb, any help would be greatly appreciated.

How can you specify the color Orange for the Interior color of a cell?

Iv'e tried with .Interior.Color = RGB(255, 153, 0)
This resulted in a Yellow color

Iv'e tried with .Interior.ColorIndex = 45 and 44
this resulted in a brownish color

Anyone else has had this problem?


Shortly after posting, I have found the solution.

ActiveWorkbook.Colors(45) = RGB(255, 153, 0) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This will set the 45th color of the 56 color palette to Orange, the color it should have been in the first place.


I was asked to find a way to define the active(!) cell to be in light green background.

I tried:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End Sub
which works fine BUT all previous "visited" cells remain green as well.

What I am looking for is a similar code which will "remember" the previous visited cell address and interior color and upon leaving that cell it will get back its previous interior color.

In addition, is there a way to emphasize the black frame around the selected cell which becomes very unclear to notice, especially, when the cell was formatted with an heavy black frame around it.

Thanks in advance,

If anyone could help i'm looking for a macro that will count certain interior colors within a selected area and place the results in a certain cell.
heres the colors and the cell i need the result in:

Interior.ColorIndex = 7 P10
Interior.ColorIndex = 46 R10
Interior.ColorIndex = 8 T10
Interior.ColorIndex = 44 V10
Interior.ColorIndex = 43 X10
Interior.ColorIndex = 4 Z10

Any help would be great.


I must be thinking about this one too hard. I want to get the interior color (that I assign) from the next cell over and apply it to the cell I'm in. It's quite straight-forward to read text from the cell next door, or do conditional formatting but I cannot figure out how to read and copy only the background color of another cell. My VBA is not strong and I normally have to resort to copying someone else's code, but have not found one for this. If anyone can help I would be very grateful. Thank you.

I need to use colors used in a userform also to be used on a sheet.
Now I need to know the numbers for the colors, but they vary. Is there anyway to find out what the numbers are for each color in sheet and userform? And are all colors even available for both, sheet and userform?

For example:

.BackColor = 255 ''Red in UserForm
.Interior.ColorIndex = 3 ''Red on Sheet

Hope someone can help...


Is it possible to chane the interior color of a text depending upon the
length of text?
If the text is just fitting the cell we can change the interior color.
But if the text extends to the next cell how we can identify this? I
dont want to change the column width of course.

so what i am looking for is a way to identify the text length and if it
streches to next cells then change the interior color of those cells


I have some code including the following statement. It worked fine for months
but all of a sudden I'm getting an error message from it. I have not changed
the names of the worksheets or locations of the cells. The With statement
this is in the middle of represents the "Crew" worksheet.

activeworkbook.worksheets("Crew") .Range(.Cells(2, 12), .Cells(4,
12)).Interior.Color = RGB(180, 180, 180)

"Unable to set the Clor property of the interior class."

Any ideas on why this suddenly no longer works?


there are d16~d28 cells of cell interior color.
Three(d16,d19,d26) of them have yellow color.
i wanna know count of yellow using xl formula
i wanna make xl embedded formula in d29 cell.

i did as follow.
name definition macro function == =GET.CELL(38,Sheet1!$D16)
name is "yel"
formula in e16~e28 are =yel respectively, thus e16~e28 cell have 0, 40,
6, 3, 5 and so on.
and then
d29 cell, =COUNTIF(E16:E28,6)
but i don't wanna use E column.
at once, i wanna compose the formula in d29 cell using macro function in
name definition without VBA.


*** Sent via Developersdex ***
Don't just participate in USENET...get rewarded for it!

Hi, I need some help defining colors of my textboxes.
I have 7 textboxes, named 'txtFbRating' and a number from 1 to 7 behind this name.
(txtFbRating1, txtFbRating2 etc)
I want to have the interiorcolor to change with the value within the textbox. Values from 1 to 9
For example, if the value is 3, I want the interiorcolor to be RGB(240, 100, 25).

I've tried to code it, but it won't work, can someone help me out here?
here's what I've tried:
Private Sub FBrating()

Dim tbox As String
Dim nr As Integer
nr = 0 - 7
tbox.TypeName = TextBox
tbox.Name = "txtFbRating" & nr

For Each tbox In MultiPage

If tbox.Value = "1" Then
    tbox.Interior.Color = RGB(165, 0, 0)
ElseIf tbox.Value = "2" Then
    txtboxname.Interior.Color = RGB(205, 0, 0)
ElseIf tbox.Value = "3" Then
    txtboxname.Interior.Color = RGB(240, 100, 25)
ElseIf tbox.Value = "4" Then
    txtboxname.Interior.Color = RGB(255, 185, 30)
ElseIf tbox.Value = "5" Then
    txtboxname.Interior.Color = RGB(204, 255, 255)
ElseIf tbox.Value = "6" Then
    txtboxname.Interior.Color = RGB(120, 205, 200)
ElseIf tbox.Value = "7" Then
    txtboxname.Interior.Color = RGB(140, 200, 65)
ElseIf tbox.Value = "8" Then
    txtboxname.Interior.Color = RGB(90, 135, 40)
ElseIf tbox.Value = "9" Then
    txtboxname.Interior.Color = RGB(45, 115, 30)

End If
End If

End Sub
regards, Trudie

Hi, Everyone

Is it possible to create UDF for Interior color of cell?

For instance, =Color(A1;3)

3 is the index of red color.

Thanks, for your attention.


I'm trying to make a macro which will sum all the cells which have a special interior color.

However I don't know how to sum these cells. I will also need to check if the Cell contains a number (ISNUMBER).

Do anyone know how I can do this,

As always thanks.

For Each Cell In Selection
On Error Resume Next
If Cell.Interior.ColorIndex = 36 Then
If Cell = ISNUMBER then
End if

End If

Next Cell

Hi All,

i have loads of oval shapes on a sheet and i am trying to loop through them when i open the workbook and change the interior color to red.

however i'm getting an error and i can't figure out why?

Sub Workbook_Open()
Dim Shp As Shape

For Each Shp In ThisWorkbook.Sheets("Game")
    Shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
Next Shp

End Sub
any ideas why?

cheers for reading all

I am having a problem getting this to work and would appreciate your input.

In "Module1":

Function SetBackgroundColor(Parameter As Range) As String
Dim xlRange As Range

SetBackgroundColor = ""

If (TypeName(Application.Caller) = "Range") Then
Set xlRange = Application.Caller
xlRange.Interior.Color = ValueToRGB(CInt(Parameter.Value))
End If

End Function

Here "ValueToRGB" simply has a Select Case statement that outputs specific
RGB (long) values based on input. This function works well and has been

The problem is that "SetBackgroundColor" never changes the background color
of the target cell.

I've even tried such code as:

[A1].Interior.Color = ValueToRGB(1)

[A2].Interior.Color = ValueToRGB(2)

[A3].Interior.Color = ValueToRGB(3)

to no avail.

However, if the above three lines are place into a public Sub that is called
by hand (By executing the macro via ALT-F8 (Tools/Macro/Macros) all three
cells are colorized perfectly.

For some reason setting the interior color does not work from a user
function, regardless of whether the target is the function within which the
function is being called or an entirely different (even hard-coded, rather
than passed or calculated) cell.

Any ideas?



Is there a way for me to define a range based on a particular interior
color index value? For example, can I "select" a group of cells on a
sheet that have a color index of 38?

Thanks in advance for any help!

I've developed a routine with brute force rather than finesse that
will change the interior color of a cell in a column if it is within a
certain percentage of either the highest or lowest value in the column

It works, but I'm sure there is a better (more efficient, use of
parameters, use of variables, etc.) or more standardized way of
accomplishing the same.

Could someone please show me the better way? Maybe there's already a
routine out there to do the same.


The routine is called with:

Call HLCell("F5", 1000, True, 4.5, 22)

rngTop is the column starting cell
cntRows is the # of rows to include in the range
bSrchHigh is for checking either highest or lowest
dPct is percentage from high or low
iColor is the color to highlight the cell

Public Sub HLCell(ByVal rngTop As Range, _
ByVal cntRows As Long, _
ByVal bSrchHigh As Boolean, _
ByVal dPct As Double, _
ByVal iColor As Integer)
Dim rngWork As Range
Dim rngWork1 As Range
Dim dMin As Double
Dim dMax As Double
Dim dLowVal As Double
Dim dHighVal As Double
Dim iRow As Integer
Dim iRowCnt As Integer
Dim idx As Integer
Set rngWork = rngTop
Set rngWork1 = rngWork.Offset(cntRows, 0)

iRow = rngWork.Offset(cntRows, 0).End(xlUp).Row
iRowCnt = iRow - rngWork.Row
dMin = 0
dMax = 0
If IsNumeric(rngWork) Then
dMin = rngWork
dMax = rngWork
End If
For idx = 0 To iRowCnt
If IsNumeric(rngWork.Offset(idx, 0)) Then
If rngWork.Offset(idx, 0) > dMax Then
dMax = rngWork.Offset(idx, 0)
End If
If rngWork.Offset(idx, 0) < dMin Then
dMin = rngWork.Offset(idx, 0)
End If
End If
rngWork.Offset(idx, 0).Interior.ColorIndex = xlNone
dHighVal = dMax * (dPct / 100)
dMax = dMax - dHighVal
dLowVal = dMin * (dPct / 100)
dMin = dMin + dLowVal
For idx = 0 To iRowCnt
If IsNumeric(rngWork.Offset(idx, 0)) Then
If bSrchHigh Then
If rngWork.Offset(idx, 0) >= dMax Then
rngWork.Offset(idx, 0).Interior.ColorIndex = _
iColor dk
End If
If rngWork.Offset(idx, 0) <= dMin Then
rngWork.Offset(idx, 0).Interior.ColorIndex = _
End If
End If
End If
End Sub


I have the following - strange - problem.

I want to set the interior color of a range object in VBA. So far so
good. I created a code and added it to the click event of a button. And
it works fine:

Worksheets("Sheet3").Range("R1").Interior.ColorIndex = 17

But I want to have this code somewhere else. I wrote a class which is
called via a function, which is embedded in a cell. The function of the
cell is =fctXY(a;b;c) etc...

This function uses my object which calls the function that changes the
cell color. But nothing happens. The color doesn't change? I already
tried to turn the automatic cell calculation off, but the same result -
nothing happens. I also can not write a value into another cell when
I'm in the class function which is called by the formula in the cells

Can someone tell me why? Is there a lock for all the cells while the
formula is calculated? Can I turn this off?

Thank you for your help!


Hi folks, I’ve gotten great help here before and am hoping that you can help me again. I have a piece of code that sets the interior color of a non-contiguous range of cells to green, 35. It works but I’m sure it could be a lot cleaner, I just don’t have the experience to make it cleaner. This piece of code runs within a looping procedure that loops through several worksheets. MyWrkSht is a variable passed to the procedure that represents the worksheet name, column is a variable that represents a column number that the procedure determines is the correct column to set the interior color. So, any simplified code would need to be able to use these two variables. Thanks in advance for the help.

With Worksheets(MyWrkSht)
                            .Range(.Cells(7, Column), _
                                .Cells(32, Column)).Interior.ColorIndex = 35
                            .Range(.Cells(34, Column), _
                                .Cells(42, Column)).Interior.ColorIndex = 35
                            .Range(.Cells(45, Column), _
                                .Cells(45, Column)).Interior.ColorIndex = 35
                            .Range(.Cells(53, Column), _
                                .Cells(78, Column)).Interior.ColorIndex = 35
                            .Range(.Cells(91, Column), _
                                .Cells(91, Column)).Interior.ColorIndex = 35
                            .Range(.Cells(99, Column), _
                                .Cells(124, Column)).Interior.ColorIndex = 35
                            .Range(.Cells(137, Column), _
                                .Cells(137, Column)).Interior.ColorIndex = 35
                      End With


Is it possible to count the nuber of rows in a range with interior
color (any)

for eg. on range "B1:F20" if any cell in B1:F1 got interior color count
it and check next row B2:F2 ... like wise and count the number of rows
in the range having atleast one cell with interior color.


I am trying to have the code take a range of cells and compare each row (each row contains a date) within that range to a cell and if the date contained in the row is before the current date to fill that row with an interior color. I am still rather new to VBA so likely I am just making some silly assumtions about how to word the coding. Thanks tons in advance for any help.

Private Sub Highlight_Click()
Dim Current_Date As Date
Current_Date = Worksheets("Name").Range("H1").Value

LastRow = [A5].End(xlDown).Row
ActiveSheet.Range("A5:H" & LastRow).Select
If Range("E5").Value <= Current_Date Then Row.Select

With ActiveRow.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Do Until Cell.Value Is Empty

End Sub

Hey guys

I have coded a function which fades the cells with colored background back to white by setting range.interior.color.

However, when I do this, these cells lose their gridlines. What do I have to do to avoid this?

I have attached an image, which shows cells with gridlines alongside cells which are "missing" their gridlines.


I've attached a workbook to explain what I mean, but I would like to be able to search a range (A4:C65536) for Interior.Color = xlNone and for empty values.

For all cells with Interior.Color = xlNone, call the range "Expenses". In the attached workbook, this range will be A4:C9.

Within the "Expenses" range, delete all rows that do not contain values. In the attached workbook, these rows will be 4, 8, 9.

Thanks very much

No luck finding an answer? You could always try Google.