Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Rgb

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,
How do i get a VBA macro/function to look at the interior colour of a worksheet cell and decompose the RGB value of the interior colour and return either the red, green or blue component value?

Thanks
Alex

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Output the Worksheet Name in a Cell in Excel - UDF
- UDF to return the name of the worksheet in Excel on which this function is located. There are no arguments for this fun
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m

Similar Topics







I'm having problms getting a loop working. I'm sure i'm mising something basic, but i can't see it.
The loop should take each cell in the range "admin", divide the date serial number within each cell (although formatted to 1/10/2008 is the first one, each susequent one is a day later) and return the remainder using "mod" to assign a colour to the cell 2 cells below.
If anyone can spare a few minutes to show me where my errors are, that would be great.

Dim admin As Range
Dim colour As Variant
Dim cell As Range
Set admin = Range("B2:AF2,B6:AF6,B10:AF10,B14:AF14,B18:AF18,B22:AF22,B26:AF26,B30:AF30,B34:AF34,B38:AF38,B42:AF42,B46:AF46,B50:AF50")

For Each cell In admin
colour.Value = cell.Value Mod 8
If colour = 3 Or 4 Then
cell.Offset(2, 0).Interior.ColorIndex = 3 'red
If colour = 5 Or 6 Then
cell.Offset(2, 0).Interior.ColorIndex = 10 'green
If colour = 7 Or 0 Then
cell.Offset(2, 0).Interior.ColorIndex = 5 'blue
If colour = 1 Or 2 Then
cell.Offset(2, 0).Interior.ColorIndex = 2 'white
End If
End If
End If
End If
Next cell


Help

I have had some help on colour previously but this is my query now,

Does anyone know if it is possible to write a macro to divide all cells by two which have a particular interior colour IE light blue.

Thanks


Hi

Can anyone tell me if there is an easy way of finding out the number for interior colour within a cell. IE for instance I know bright green is 4 but want to now other colour index's,

Cheers
Kate


Good morning,

I'm trying to get a cell to update its' background colour based on the value of another cell. Although the value of the cell changes, the macro to change the background colour to change does not seem to get invoked.

My code, contained withing the sheet, is:

Code:

Private Sub Workbook_SheetSelectionChange(ByVal Target As Excel.Range)

' Change colour of cell dependant on value.
' To do: Colour of cell does not change when D7 dropdown alters, even though values within the cell are updated.
    Application.EnableEvents = False
    Dim rng As Range
    Set rng = Intersect(Target, Range("F7:K67, M7:S67, U7:Y67"))
    If rng Is Nothing Then
        Exit Sub
    Else
        Dim cl As Range
        For Each cl In rng
            Select Case cl.Text
            Case "F" ' Light Green
                cl.Interior.ColorIndex = 35
            Case "U" ' 25% Grey
                cl.Interior.ColorIndex = 15
            Case "O" ' Tan
                cl.Interior.ColorIndex = 40
            Case "R" ' Pale Blue
                cl.Interior.ColorIndex = 37
            Case "T" ' Light Yellow
                cl.Interior.ColorIndex = 36
                
            ' Now define status codes
            Case "M" ' Rose
                cl.Interior.ColorIndex = 38
            Case "S" ' 
                cl.Interior.ColorIndex = 45
            Case "-" ' Grey 80%
                cl.Interior.ColorIndex = 56
                
            Case Else
                cl.Interior.ColorIndex = 2
Finish:                     Application.EnableEvents = True
                Exit Sub
            End Select
        Next cl
    End If
End Sub


This works if I manually enter the cell value, but not if the cell value is updated via a formula, which is:

Code:

=IF($D7="Maternity Leave","M",IF($D7="Secondment","S",IF($D7="Unavailable","-",UnderwriterData!F7)))


I've searched Google and these forums to no avail. Any ideas if a 'fix' is available? (ideally via VBA that I can just apend to my existing code).

Thanks!


Hi Guys,

Am just getting confused here....the idea is to have colour coding as below:

> 72 hrs = cell is green
between 0 and 24 hrs = cell is red
between 24 and 72 = yellow
0 or less than zero = cell is light blue
if no value or blank in cell = cell is white or no colour (but when value is i/p colour changes).....

since the values are calculated as difference in time (whatever was open time - current time to give current remaining life)..... could you please help with the code i posted ....seems am mixed up:

Private Sub Worksheet_Calculate()
For Each rCell In Range("F9:F10000")
Select Case rCell.Value
Case Is = ""
rCell.Interior.ColorIndex = 2
Case Is > 72
rCell.Interior.ColorIndex = 3
Case Is <= 24
rCell.Interior.ColorIndex = 4
Case Is > 24 < 72
rCell.Interior.ColorIndex = 5
Case Else
rCell.Interior.ColorIndex = xlColorIndexNone
End Select
Next rCell
End Sub


i'm having a little problem and i hope someone can help? (please!) - i have the following code - which works well:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Range("ACTIVITY1")
If Cell.Value = "Not Started" Then _
Cell.Interior.Color = RGB(0, 255, 0)
If Cell.Value = "In Progress" Then _
Cell.Interior.Color = RGB(0, 190, 0)
If Cell.Value = "Completed" Then _
Cell.Interior.Color = RGB(0, 255, 0)
If Cell.Value = "At Risk" Then _
Cell.Interior.Color = RGB(150, 150, 0)
If Cell.Value = "Suspended" Then _
Cell.Interior.Color = RGB(150, 150, 0)
If Cell.Value = "Withdrawn" Then _
Cell.Interior.Color = RGB(150, 150, 0)
If Cell.Value = "" Then _
Cell.Interior.Color = RGB(255, 255, 255)
Next Cell
End Sub


I also want to add in a condition that checks if the cell in the above range equals "Not Started" and the date (which is in the next column) is greater than or equal to today and then changes colour (to orange instead of green).. can anyone help?


Hi,

I have created a basic loop to change the background colour of cells depending on the value in the cell and this works fine. However I want the worksheet to be able to update the background colour "on the fly" or as the number is selected from a list or changed in each specific in the range rather than having to run the macro after the changes have been made. I have included the code I am using below so you can understand what I am doing and hopefully what I want to do - any help much appreciated.

Sub colour()
For Each oCell In Range("H8:N3280")
oCell.Interior.ColorIndex = xlNone
Next oCell
For Each oCell In Range("H8:N3280")
Select Case oCell.Value
Case Is = 1
oCell.Interior.ColorIndex = 35
Case Is = 2
oCell.Interior.ColorIndex = 36
Case Is = 3
oCell.Interior.ColorIndex = 45
Case Is = 4
oCell.Interior.ColorIndex = 3
Case Is = 5
oCell.Interior.ColorIndex = 9
End Select
Next oCell
End Sub


Using defined RGB colors using the statement 'activecell.interior.color = rgb(n,n,n)' does not always colour the cell. I have been trying to specifically colour cells within a project without consistent success.

From the immediate window, I tested and received the following results

activecell.interior.color = rgb(0,0,255)
?showrgb2(activecell)
0, 0, 255
activecell.interior.color = rgb(214,209,255)
?showrgb2(activecell)
192, 192, 255

rgb(0,0,255) (blue) works fine but Excel seems to change rgb(214,209,255) (light blue) to rgb(192,192,255)....a few shades darker which is not what I want.

I do not want to use the pallete as I have experienced issues when copying data from other workbooks (which presumably have different palletes) which consequently changes colours in the receiving sheet.

I am seeking to consistently colour cells with my custom selections many of which are lighter than than those in the pallete. Is there a limitation on rgb colours Excel will recognise?

Paul


Hi People,

I'm using a code that looks for colour 48 and replaces it with colour 15 and that works fine, but I would like to check for all colours except 'No colour', 'Colour 6' and 'Colour 8" and change them to 'Colour 15'. I don't know how to add the exclsion of None, 6 & 8 to my code.

Any help greatly appreciated, here's my code.

Code:

Sub Change_Grey()
Dim r As Range
For Each ws In Sheets
    ws.Activate
For Each r In ActiveSheet.UsedRange
    If r.Interior.ColorIndex = xlNone Then
    Else: r.Interior.ColorIndex = 15
End If
Next r
Next ws
End Sub





Hi there,

I have a spreadsheet with a number of inputs in it.

All of the input cells have an Interior Colour of 6 (yellow).

Everything else either has a formula, is a fixed digit, or is empty.

What I want....Is to create a macro so that everything with interior colour is NOT locked/protected....and everything else is.

The following was suggested to me...but is still a little slow:

Private Sub CommandButton1_Click()

Dim x As Integer
Dim y As Integer

For x = 1 To 100
For y = 1 To 100
If Cells(x, y).Interior.ColorIndex = 6 Then
Cells(x, y).Locked = False
Else: Cells(x, y).Locked = True
End If

Next y
Next x

End Sub

Thanks for your advice


how can i set the font colour to contrast with the interior colour of a cell. my routine randomly selects a colour for the Conditional Format so I don't know what it will be each time.


Hi,
Wonder if someone is willing to take the challange of this one....

I have for example the following report which needs the price filled in....
Colour Depth price
green 15
blue 19
yellow 111
red 2
green 19
blue 6
yellow 9
red 10

now what i would like to do... is match the following against it...
I have the following separate tables....
Colour Depth price
blue 19 6.5
blue 6 7.4

green 15 8.3
green 19 9.2

red 2 8.1
red 10 3.2

yellow 111 86
yellow 9 91

for example....blue is the colour that has a depth of 19 and 19 has a price of 6.5.... the 19 can easily be done in a vlookup however the value of 19 in green red and yellow would be different.

so that i would get the combination of the colour and the depth to fill in the price...thus...
Colour Depth Price
green 15 8.3
blue 19 6.5
yellow 111 86
red 2 8.1
green 19 9.2
blue 6 7.4
yellow 9 91
red 10 3.2


I hope that i have explained this okay and it makes a bit of sense...and i could really use the help on this one...

see the file attached for the excel version...


Thanks in advance all help is appreciated....


I have the following code that changes the interior of a cell to green:

Selection.Interior.Color = 5287936
Does anyone know whether it is possible to have the code so that it selects all of the current worksheet and does the same and not just the cell the cursor is in when this is run?

Many thanks.


Hi all,

I have a list of colours in a column that look like the following examples

LIGHT BLUE/GREY
BLUE/GREY
CHERRY RED/BLACK
SILVER
LIGHT GREY

They represent vehicle exterior colours and after the "/" this is the
interior colour. As you can see some have the interior after the slash and
some don't have the interior colour but have two words for the exterior. I
need to extract the exterior colour only as the following.

LIGHT BLUE
BLUE
CHERRY RED
SILVER
LIGHT GREY

I have tried the "find" and the "substitute" formula's but can't manage to
get it right.

Please help

Thank you



Hello

I have found a macro that allows me to assign a colour to a specific brand. Does anyone know how I can alter this to apply the same formatting across a number of charts and not just the active chart...

The macro is

Dim iPoint As Long, nPoint As Long
With ActiveChart.SeriesCollection(1)
For iPoint = 1 To .Points.Count
Select Case WorksheetFunction.Index(.XValues, iPoint)
Case "AAA"
.Points(iPoint).Interior.Color = RGB(0, 176, 240) ' Dark Blue
Case "BBB"
.Points(iPoint).Interior.Color = RGB(0, 112, 192) ' Light Blue
Case "CCC"
.Points(iPoint).Interior.Color = RGB(247, 150, 70) ' Green
Case "DDD"
.Points(iPoint).Interior.Color = RGB(192, 0, 0) ' Red
Case "EEEE"
.Points(iPoint).Interior.Color = RGB(0, 176, 80) ' Orange
Case "FFFF"
.Points(iPoint).Interior.Color = RGB(49, 133, 156) ' Green
Case "GGGG"
.Points(iPoint).Interior.Color = RGB(0, 176, 246) ' Cyan
Case "IIIII"
.Points(iPoint).Interior.Color = RGB(238, 236, 225) 'Pink
Case "JJJJJ"
.Points(iPoint).Interior.Color = RGB(255,0,0) 'Pink
End Select
Next
End With
End Sub


Thanks in advance


in reference to: http://www.mrexcel.com/board2/viewtopic.php?t=277129

I have a little macro that looks at a specific cell, and then copies the colour of that cell to the CONDITIONAL format colour for the cell. That is, if the initial cell in the row is Red, the macro will then choose red as the colour that will format the row following if the conditional format condition is met. If the next row cell is blue, the conditional format will be changed to blue.

How do i modify the following macro to start at the first coloured cell and run until it gets to the last coloured cell. In this piece, the ActiveCell is the same cell as ColouredCell which is a public variable named in an earlier routine. The earlier routine assigns a random colour to the ColouredCell.

Code:

Sub CondForm()

    Dim cel As Range

     'identify range containing conditional format
    Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 61)).Select
    For Each cel In Selection
        If cel.FormatConditions.Count > 0 Then
            'Replace the interior (highlight) color
            cel.FormatConditions(1).Interior.ColorIndex = ColouredCell.Interior.ColorIndex
        End If
    Next cel

End Sub





I have this code:

Code:

Private Sub WorkSheet_SelectionChange(ByVal Target As Excel.Range)
Static OldRange As Range
On Error Resume Next
Target.Interior.ColorIndex = 35 ' 35=pale green
OldRange.Interior.ColorIndex = xlColorIndexNone
Set OldRange = Target

End Sub


The problem is this, some cells are already coloured, not all the same colours. When I select them they change to pale green as I want but then get changed to NONE when I select another cell. I know why but not how to stop it. Is there a way to have them return to their original colour? If they were all same colour I would know how.

Also, when I protect the sheet, the macro has no effect, can this be changed so it does work on protected sheet.

Thanks for any help.


hello.

I have got some code to change colour of a column of cells H8:H301 depending upon dates in that range having certain criteria in relationship to cell H6.

But what i am trying to do is get all cells in Range A8:J301 also to change colour but still using Column H as to what i am checking against, please see code below and if any one can help me add what is needed i would appreciate this greatly

Code:

Dim c As Range, rng
Set rng = Range("H8:H301")

For Each c In rng

If c.Value < Range("H6").Value Then
c.Interior.ColorIndex = 2 ' Colours Cells White
c.Interior.Pattern = xlSolid
End If

If c.Value >= Range("H6").Value Then
c.Interior.ColorIndex = 4 ' Colours cells Green
c.Interior.Pattern = xlSolid
End If

If c.Value >= Range("H6").Value + 7 Then
c.Interior.ColorIndex = 8 ' Colours cells Cyan
c.Interior.Pattern = xlSolid
End If

If c.Value >= Range("H6").Value + 14 Then
c.Interior.ColorIndex = 46 ' Colours cells Orange
c.Interior.Pattern = xlSolid
End If

If c.Value >= Range("H6").Value + 21 Then
c.Interior.ColorIndex = 27 ' Colours cells Yellow
c.Interior.Pattern = xlSolid
End If

If c.Value >= Range("H6").Value + 28 Then
c.Interior.ColorIndex = 15 ' Colours cells Grey
c.Interior.Pattern = xlSolid
End If

If c.Value >= Range("H6").Value + 35 Then
c.Interior.ColorIndex = 2 ' Colours cells White
c.Interior.Pattern = xlSolid
End If



Next c


Cheers


Morning everyone,

I am trying to set up a macro which searches for a particular text string and colours the cell in when this text string is found.

At the moment, I have got the macro to search for the cell and currently it colours the cell in Green.

I now want to develop the macro so when I fill in Cell B2 in a colour I want by using the fill function in Excel, the macro when run, determines what colour this is and fills all cells that contain the predefined text string in, with this colour.

At the moment, in the macro, the colour is defined as follows:

With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With

Many thanks!


I have a spreadsheet that has a cell defined as:

I33 the cell colour is formatted as Green (green is the default colour signifying do not populate this cell.

The Conditional Format is:
=if(and(H33<>Blank_Cell),1,0) then format I33 as cell colour yellow

What I am challenged with is when cell I33 is finally populated with text I wish the cell colour to become blue.

Start:

The sequence then will be that H33 is blank then I33 colour is green

Action step is H33 is populated with a value (numerical) therefore I33 needs to be populated now so it's colour needs to change to yellow (meaning data is required in this cell (text).

The result of populating cell I33 now should change the colour to blue.

How do I do this?


Hi,

I am trying to build a function to format the colour of one cell to the same as another cell, but its driving me mad as its been a while since ive had to program anything. The code I have tried is included below, however i know that it's way off the mark!

Any help would be greately appreciated.
Code:

 
Function Colour(Cellval As Range, CurCell As Range)
  
    CellColor = Cellval.Interior.ColorIndex
    Colour = CellColor
    Range(CurCell).Select
    With Selection.Interior
        .ColorIndex = CellColor
        .Pattern = xlSolid
    End With
    
End Function






Dear All,
i have a little problem/puzzle im hoping somebody could solve for me
On Sheet1 In Column A starting in Row1 going downwards i have cells of different colours but this could be as little as 6 and as many as 30 they Only appear once and each colour has a Unique 3 Letter code in it
On Sheet2 In Column A starting in Row1 there is a 3 digit number for each colour ......... 2 or Colours could have the same 3 digit number but 99% of the time the number will be unique to the colour


On Sheet2 In column A starting in Row 1 going downwards i have up to 180 rows all mixed up of the colours in Row A with out the unique 3 letter code.
Could anybody write some small code to try and sort out to start on sheet 2 Cell A1 find out what colour the cell is and "look" for it find the colour and copy the colour and 3 Letter code to Sheet 2 Column A and also copy the 3 digit number to column B

eg

Sheet 1


A B
1 ABC 123 A1 CELL COLOUR IS RED
2 LMN 456 B1 CELL COLOUR IS GREEN
3 XYZ 789 C1 CELL COLOUR IS YELLOW
4 PWS 194 D1 CELL COLOUR IS GREY
5 ZSX 358 E1 CELL COLOUR IS BLUE


Sheet 2
A B
1 A1 CELL COLOUR IS RED
2 B1 CELL COLOUR IS GREEN
3 C1 CELL COLOUR IS YELLOW
4 D1 CELL COLOUR IS GREY
5 E1 CELL COLOUR IS BLUE
6 F1 CELL COLOUR IS YELLOW
7 G1 CELL COLOUR IS RED
8 H1 CELL COLOUR IS BLUE
9 I1 CELL COLOUR IS BLUE
10 J1 CELL COLOUR IS BLUE
11 K1 CELL COLOUR IS GREY
12 L1 CELL COLOUR IS GREEN
13 ETC ETC ETC



Many thanks in advance



Steve







Can anyone tell m firstly where I can find the colour pallette codes for commands like, as my suck it see method of guessing the right colour is pretty time cosuming.
selection.Interior.ColorIndex = 34

and secondly, can rgb be used to fill cells colour i.e. something like
range.colour or background whatever the command is. rgb(255,255,255)

many thanks


I wanted to change the colour of the Auto Filter Arrow from blue to a brighter colour, but found out it is not possible.

I did come across the following code which when place in a worksheet module, changes the colour of the Cell of the selected autofilter cells to yellow.

Can anyone please comment the code so I can understand it more thoroughly.
Green comments I are what I think I understand...
Red Comments I would like to understand...

Any explanation to help me understand the code would be appreciated, thanks in advance.

Code:

Private Sub Worksheet_Calculate()
 ' Declare Variables..........  
  Dim af As AutoFilter
  Dim fFilter As Filter
  Dim iFilterCount As Integer
 
 ' When active sheet has autoFilter mode on then........???? 
  If ActiveSheet.AutoFilterMode Then
    Set af = ActiveSheet.AutoFilter
    iFilterCount = 1 
    For Each fFilter In af.Filters
    ' If Filter on then change selected filter colour index to yellow (ColorIndex = 6)....    
        If fFilter.On Then
        af.Range. Cells(1, iFilterCount)  _
          .Interior.ColorIndex = 6
      Else
        af.Range. Cells(1, iFilterCount)  _
          .Interior.ColorIndex = xlNone
      End If
       iFilterCount = iFilterCount + 1 
    Next fFilter
  Else
 'Otherwise, no color change... 
Rows(1).EntireRow.Interior.ColorIndex = xlNone
  End If
End Sub





Hi,

I would like some help in writing a macro to allow a few things to happen within a excel worksheet.

I have a spreadsheet detailing when something has been completed, and into a cell you will put a date and change cell colour, if green, then I would like the macro to input a date +84 days to the date that has been inputted, in a cell 3 to the right of it, and change that cell colour to blue, which to me would indicate the next due date.But if the colour was red then it needs a date +30 into the cell next to it on the right.

What I also need is it to ignore placing the blue cell if you then change that to green, i.e it's been completed.

I need this to look at all cells within workbook, if cell is a date.

any help would be appreciated.

cheers
Paul