• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Excel Colours

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
            PlayTheSound "tada.wav"
            Cells(1, 17).Interior.Color = 255
            Point02 = True
          Exit For
        End If
    End If
Next



Cheers
0
cpatte7372
Asked:
cpatte7372
  • 7
  • 5
  • 5
  • +1
1 Solution
 
Rory ArchibaldCommented:
Use ColorIndex instead of Color and use values between 1 and 56.
0
 
Chris BottomleyCommented:
What have you changed ...

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

Is red, (255)

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

Chris
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
cpatte7372Author Commented:
I tried various numbers e.g. 247, 242 and it still appears red
0
 
Chris BottomleyCommented:
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
 
Rory ArchibaldCommented:
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
 
Chris BottomleyCommented:
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
 
ComputerAidNZCommented:
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
 
ComputerAidNZCommented:
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
 
Chris BottomleyCommented:
Note standard colors:

vbBlack
vbBlue
vbCyan
vbGreen
vbMagenta
vbRed
vbWhite
vbYellow

Chris
0
 
Rory ArchibaldCommented:
>>"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
 
Rory ArchibaldCommented:
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
 
cpatte7372Author Commented:
Hi Chaps,

Going through your responses now.....

Cheers
0
 
ComputerAidNZCommented:
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
 
cpatte7372Author Commented:
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
 
Chris BottomleyCommented:
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

Open in new window

0
 
Chris BottomleyCommented:
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

Open in new window

0
 
Rory ArchibaldCommented:
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
 
ComputerAidNZCommented:
If Not Point02 Then Cells(1, 4).Interior.Color = RGB(102, 255, 51)
0
 
ComputerAidNZCommented:
or
Cells(1, 4).Interior.Color = RGB(102, 255, 51)
0
 
cpatte7372Author Commented:
Thanks man
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now