Solved

# Excel Colours

Posted on 2011-03-03
291 Views
Hi Experts,

Very simple one for experts.

Can someone please show me how to change the colours in the vba?

They all apear RED on the spreadsheet. I've tried changing the numbers but they keep on highlighting RED.

Cheers

For Each xCell In Range("r2:r" & xLastRow)
xValue = xCell.Value
xValue2 = xCell.Offset(0, -14).Value
If Not IsError(xValue) And Not IsError(xValue2) Then
If xValue <> "" And xValue2 <> "" And Round(xValue, 2) = Round(xValue2, 2) Then
PlayTheSound "windows xp information bar.wav"
Cells(1, 18).Interior.Color = 255
Exit Sub
End If
End If
Next

Cells(1, 18).Interior.Color = 65535
If Not Point02 Then Cells(1, 12).Interior.Color = 65535

For Each xCell In Range("l2:l" & xLastRow)
xValue = xCell.Value
xValue2 = xCell.Offset(0, -8).Value
If Not IsError(xValue) And Not IsError(xValue2) Then
If xValue <> "" And xValue2 <> "" And Round(xValue, 2) = Round(xValue2, 2) Then
PlayTheSound "Windows Information Bar.wav"
Cells(1, 12).Interior.Color = 255
Exit Sub
End If
End If
Next

Cells(1, 12).Interior.Color = 65535

If Not Point02 Then Cells(1, 17).Interior.Color = 65535
For Each xCell In Range("Q2:Q" & xLastRow)
xValue = xCell.Value
If Not IsError(xValue) Then
If xValue <> "" And xValue = 0# Then
Cells(1, 17).Interior.Color = 255
Point02 = True
Exit For
End If
End If
Next

Cheers
0
Question by:cpatte7372
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 5
• 5
• +1

LVL 85

Expert Comment

ID: 35026276
Use ColorIndex instead of Color and use values between 1 and 56.
0

LVL 59

Expert Comment

ID: 35026277
What have you changed ...

Cells(1, 18).Interior.Color = 255

Is red, (255)

Chris
0

LVL 59

Expert Comment

ID: 35026290
YOu can also stick with color which should not vary and use vbred for red, vbgreen for green vbblue etc.

Chris
0

Author Comment

ID: 35026327
I tried various numbers e.g. 247, 242 and it still appears red
0

LVL 59

Expert Comment

ID: 35026355
The color choice is limited somewhat, vbred/vbgreen etc will definitely work and relate to long type numbers.  ALso you can use the palette but unless it is reset each time the users can change it.

You can also try teh RGB value via:
.Cells(1, 1).Interior.Color = rgb(255,0,0) for example as red.

Chris
0

LVL 85

Expert Comment

ID: 35026442
247 and 242 are very very close to red and since the palette is limited to 56 colours, will simply appear red (assuming red is in the palette) The spectrum is 0 to 16777215 for the Color property (though you will just get the closest of the 56 available colours).
I personally prefer to use Colorindex since you should try and fit whatever colour scheme is in place, IMO.
0

LVL 59

Expert Comment

ID: 35026525
And who am I to argue with your HO!

I personally use the fixed color constants when I can as they are publicly obvious and the RGB values at other times as I have had too many bad experiences with the palette being altered and if I reset the palette before using it, it upsets them, (for some reason!)

Horses for courses I think and I wouldn't suggest the palette approach (colorindex) is wrong ... and I certainly wouldn't suggest Rory is wrong, not as right as me perhaps but not wrong :o)  i.e. many ways to do the job you pays yer money ...

Chris
0

LVL 2

Expert Comment

ID: 35026532
An article I've found may help you understand the issue more closely.
Hope this helps

http://www.mvps.org/dmcritchie/excel/colors.htm
0

LVL 2

Expert Comment

ID: 35026540
Or use the following as an example:

Range("A1:A6").Interior.Color = RGB(200,160,35)

RGB Colour is more precise - I've always used it.
0

LVL 59

Expert Comment

ID: 35026558
Note standard colors:

vbBlack
vbBlue
vbCyan
vbGreen
vbMagenta
vbRed
vbWhite
vbYellow

Chris
0

LVL 85

Expert Comment

ID: 35026562
>>"RGB Colour is more precise "

Yes and no. Pre 2007, you will only get the closest available colour if you are filling a cell, so there's a good chance the RGB you specify will not be what you get.

0

LVL 85

Expert Comment

ID: 35026568
Chris,
I think you forgot "swings and roundabouts" and "six of one, half a dozen of the other", but otherwise I agree. (except about my being wrong of course)
0

Author Comment

ID: 35026821
Hi Chaps,

Cheers
0

LVL 2

Expert Comment

ID: 35026901
It is surprising how diverse answers can be!
No-one is wrong per se, but it would be a particular situation that may dictate the best way to achieve a solution.
My opinion is based upon the fact I use portability, so I would take code from one product and use in another - VB, VBA, Word, Excel, Access, etc.
Therefore I don't need to know too many different ways to accomplish a task.  But there woul dbe times when using other functions would be simpler and if one doesn't know VBA too well, the the vbBlack, vbRed would be a simpler way of recognising something if you were going over code at a later stage.
Probably more importantly, is to know the limitations of each and then realise why something is not working.
From your side, I would either stick with your method and just understand a bit more about why things go wrong (hence I've sent you a link to an explanation) with that method or try another method and learn how that will fit in with your requirements.

It is so much up to personal choice and we all respect people's choices, because our way is not the only way!
0

Author Comment

ID: 35026912
Gents,

I'll be honest, I didn't get most of what you were saying, so I went to the link provided by ComputerAid.

I tried incorporating the colour lime green with the following, but it came black. Can someone show me what combination I would need for lime green?

Sub CheckD()
Dim ctr As Long
Dim Point02 As Boolean
If Not Point02 Then Cells(1, 4).Interior.Color = 65535
For ctr = 2 To 65000
If Range("D" & ctr) = "" Then Exit Sub
If Range("D" & ctr) = Round(Range("L" & ctr).Value, 2) - 0.02 Then
PlayTheSound "Windows XP Print complete.wav"
Cells(1, 4).Interior.Color = 102
End If
Next
End Sub

From the website, I should be getting lime green, but it keeps on coming out black

Cheers
0

LVL 59

Expert Comment

ID: 35027067
Option 1 via color constants replace:

Cells(1, 18).Interior.Color = vbred

with for example 1 of the following:

Cells(1, 18).Interior.Color = vbred
Cells(1, 18).Interior.Color = vbBlack
Cells(1, 18).Interior.Color = vbBlue
Cells(1, 18).Interior.Color = vbCyan
Cells(1, 18).Interior.Color = vbGreen
Cells(1, 18).Interior.Color = vbMagenta
Cells(1, 18).Interior.Color = vbWhite
Cells(1, 18).Interior.Color = vbYellow

Chris
``````For Each xCell In Range("r2:r" & xLastRow)
xValue = xCell.Value
xValue2 = xCell.Offset(0, -14).Value
If Not IsError(xValue) And Not IsError(xValue2) Then
If xValue <> "" And xValue2 <> "" And Round(xValue, 2) = Round(xValue2, 2) Then
PlayTheSound "windows xp information bar.wav"
Cells(1, 18).Interior.Color = vbred
Exit Sub
End If
End If
Next
``````
0

LVL 59

Accepted Solution

Chris Bottomley earned 500 total points
ID: 35027106
Option 1 via color constants replace:

Cells(1, 18).Interior.Color = vbred

with for example 1 of the following:

Cells(1, 18).Interior.Color = vbred
Cells(1, 18).Interior.Color = vbBlack
Cells(1, 18).Interior.Color = vbBlue
Cells(1, 18).Interior.Color = vbCyan
Cells(1, 18).Interior.Color = vbGreen
Cells(1, 18).Interior.Color = vbMagenta
Cells(1, 18).Interior.Color = vbWhite
Cells(1, 18).Interior.Color = vbYellow

Chris
``````For Each xCell In Range("r2:r" & xLastRow)
xValue = xCell.Value
xValue2 = xCell.Offset(0, -14).Value
If Not IsError(xValue) And Not IsError(xValue2) Then
If xValue <> "" And xValue2 <> "" And Round(xValue, 2) = Round(xValue2, 2) Then
PlayTheSound "windows xp information bar.wav"
Cells(1, 18).Interior.Color = vbred
Exit Sub
End If
End If
Next
``````
0

LVL 85

Expert Comment

ID: 35027116
The colour values on the web site are RGB values and you have to use all three - for lime green:
Cells(1, 18).Interior.Color = RGB(153, 204, 0)

I think.
0

LVL 2

Expert Comment

ID: 35027120
If Not Point02 Then Cells(1, 4).Interior.Color = RGB(102, 255, 51)
0

LVL 2

Expert Comment

ID: 35027128
or
Cells(1, 4).Interior.Color = RGB(102, 255, 51)
0

Author Closing Comment

ID: 35030616
Thanks man
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference texâ€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month7 days, 5 hours left to enroll