VBA color index

[walker1985]

Hi Guys
I apologise if this is in the incorrect place but was wondering if you could help, I currently have a rota that we use with a series of macro's recorded that when pressed will fill the active cell with a color and text, they work fine, the issue is they currently use color index whic as you all know is limited to a set number of colors, I want to change interior fill from color index to RGB to be able to get better color match, Here is some exsisting code which works fine I just need RGB instead of index any help would be greatly appriciated

Sub EDI()
Application.ScreenUpdating = False
Selection.Value = "EDI"
With Selection.Interior
.ColorIndex = 17
.Pattern = xlSolid
End With
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

[mancubus]

welcome to VBAX.

please use code tags when posting your code here. # button inserts code tags like [ CODE ][ /CODE ] (without spaces).

[ CODE ]Paste Your code here[ /CODE ]

that said, try:



Range("A1").Interior.Color = RGB(255, 0, 255)

[walker1985]

welcome to VBAX.

please use code tags when posting your code here. # button inserts code tags like [ CODE ][ /CODE ] (without spaces).

[ CODE ]Paste Your code here[ /CODE ]

that said, try:



Range("A1").Interior.Color = RGB(255, 0, 255)


Hi there
thanks for your reply sorry about the tags we use going forward, where in the code would I place your suggestion? must apologise I can do the bare minimum on VBA

[JKwan]

so instead of

.ColorIndex = 17

use this

.Color = RGB(255, 0, 255)

[Paul_Hossler]

1. Here's a copy of my color cheat sheet if you want

2. I added your EDI macro to it as a demo of how I've done it

3. In my 'Production' WB the color sheets are hidden (sort of like a database) to allow me to use VBA to VLookup by color name; not really necessary since the Long will work as well

4. It sort of looks like you'll have a lot of color macros (???) so I'd modularize it to save code and make maintenance easier (pvtSelectCellColor)

5. This only works on the single active cell, but if you wanted to apply it to a group of cells, that can be done also




Option Explicit

Const gcEDI As Long = 2237106
Const gcSomethingElse As Long = 25600


Sub EDI()
Call pvtSelectCellColor("EDI", gcEDI)
End Sub

Sub SomethingElse()
Call pvtSelectCellColor("SOMETHINGELSE", gcSomethingElse)
End Sub


'-----------------------------------------------------------------------------

Private Sub pvtSelectCellColor(S As String, C As Long)

If Not TypeOf Selection Is Range Then Exit Sub

Application.ScreenUpdating = False

With ActiveCell
.Value = S
With .Interior
.Color = C
.Pattern = xlSolid
End With

.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False

.Offset(1, 0).Select
End With

Application.ScreenUpdating = False
End Sub