How do I say "Make this cell blue"? 1

[OhioSteve]

In excel vba, I want to write a function that changes the cell's color based upon a string parameter. Here is my syntax:

Public Function format1(agentID As String)
Select Case agentID
Case "1000"
'make current cell blue
Case "2000"
'make current cell red
End Select
End Function

We can't use conditional formatting because we need more than three different colors. Plus we want the portability of an .xla file.

 

[AnotherHiggins]

USe your macro recorder (Tools > Macro > Record New Macro) and proceed to turn various cells different colors. Observe the recorded code to find the color index for whatever colors you want.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PHV]

You may try this:
Public Function format1(agentID As String)
With ActiveCell.Interior
Select Case agentID
Case "1000"
.ColorIndex = 5 'make current cell blue
Case "2000"
.ColorIndex = 3 'make current cell red
End Select
.Pattern = xlSolid
End With
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

I'm posting here, as this idea may come in handy with yet another project I volunteered for at work.

But I'll not be getting around to it for another week and a half or 2 weeks, at the least.

Thanks for posting the question, and thanks for the responses so far. If nothing else, it'll be food for thought!

--

"If to err is human, then I must be some kind of human!" -Me

 

[AnotherHiggins]

KJV:

If you want to keep track of a thread, you can click on the

Click Here add this thread to your archive.

link at the right side of the screen.

Other threads that might be of interest to you:
thread68-1295894
thread68-223068

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Actually, I just thought of something.... I made a custom function I used a while back that required me to list out several index numbers and their related colors.

Hope this helps:
[tt]
Index Color Name
1 Black
53 Brown
52 Olive Green
51 Dark Green
49 Dark Teal
11 Dark Blue
55 Indigo
56 Gray-80%
9 Dark Red
46 Orange
12 Dark Yellow
10 Green
14 Teal
5 Blue
47 Blue-Gray
16 Gray-50%
3 Red
45 Light Orange
43 Lime
50 Sea Green
42 Aqua
41 Light Blue
13 Violet
48 Gray-40%
7 Pink
44 Gold
6 Yellow
4 Bright Green
8 Turqoise
33 Sky Blue
54 Plum
15 Gray-25%
38 Rose
40 Tan
36 Light Yellow
35 Light Green
34 Light Turqoise
37 Pale Blue
39 Lavendar
2 White
[/tt]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Now THAR'S a TIP! ==> *

Skip,

_[red][/red]

 

[AnotherHiggins]

Thanks, Skip!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.