Solved

Excel Colours

Posted on 2011-03-03
21
283 Views
Last Modified: 2012-05-11
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
Comment
Question by:cpatte7372
  • 7
  • 5
  • 5
  • +1
21 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35026276
Use ColorIndex instead of Color and use values between 1 and 56.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35026277
What have you changed ...

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

Is red, (255)

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
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

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

Expert Comment

by:Chris Bottomley
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

by:Rory Archibald
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

by:Chris Bottomley
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

by:ComputerAidNZ
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

by:ComputerAidNZ
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

by:Chris Bottomley
ID: 35026558
Note standard colors:

vbBlack
vbBlue
vbCyan
vbGreen
vbMagenta
vbRed
vbWhite
vbYellow

Chris
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 85

Expert Comment

by:Rory Archibald
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

by:Rory Archibald
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

by:cpatte7372
ID: 35026821
Hi Chaps,

Going through your responses now.....

Cheers
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
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

by:cpatte7372
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

by:Chris Bottomley
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

Open in new window

0
 
LVL 59

Accepted Solution

by:
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

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
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

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

Expert Comment

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

Author Closing Comment

by:cpatte7372
ID: 35030616
Thanks man
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now