Previous in Forum: W200 Wireless LAN Settings of EVO N410c Notebook   Next in Forum: Push, Pull, and...?
Close
Close
Close
32 comments
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
Good Answers: 96

Excel / Visual Basic Cell Fill Color

07/29/2010 4:58 AM

Dear friends and associates,

I am trying to find a way to cause a cell's fill color to be according to data from another cell or cells.

For instance, I know that from the fill icon, I can specify the RGB color of a cell's fill, so I am hoping there is a way either directly in a formula or in VB to call for that RGB information and fill the specified cell accordingly.

Any ideas or suggestions will be helpful.

Kind regards ...

__________________
"Just when I had all the answers, they changed all the questions"
Register to Reply
Pathfinder Tags: Cell Fill Color Excel Fill Color RGB Visual Basic
User-tagged by 5 users
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be Good Answers:

These comments received enough positive ratings to make them "good answers".

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
Guru
Engineering Fields - Mechanical Engineering - New Member Hobbies - CNC - New Member

Join Date: Aug 2006
Location: Western Australia
Posts: 760
Good Answers: 58
#1

Re: Excel / Visual Basic Cell Fill Color

07/29/2010 5:47 AM

On the toolbar go to <Format> - <Conditional formatting> and see if this will do what you want to achieve.

__________________
If you can't explain it simply, you don't understand it well enough. (A.E.)
Register to Reply
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
Good Answers: 96
#3
In reply to #1

Re: Excel / Visual Basic Cell Fill Color

07/29/2010 10:52 PM

Hi Prof ...

It was a good idea, but same as the other formatting tools, it doesn't allow to call values from other cells, or at least I can't find how. But, thanks.

Kind regards ...

__________________
"Just when I had all the answers, they changed all the questions"
Register to Reply
Guru
Engineering Fields - Electrical Engineering - Analog and Digital Circuit Design Engineering Fields - Electromechanical Engineering - Transformers, Motors & Drives, EM Launchers Engineering Fields - Engineering Physics - Applied Electrical, Optical, and Mechanical

Join Date: Jan 2008
Location: NY
Posts: 1208
Good Answers: 119
#4
In reply to #3

Re: Excel / Visual Basic Cell Fill Color

07/29/2010 11:41 PM

I've used the "conditional" formatting in OpenOffice Spreadsheet (open source excel clone) and it does change cell color (and other formatting) based on a reference cell data value. You may be seeing a simple syntax problem. Try reviewing the help file for more details.

On one sheet I have cost (cells) below target automatically format a "green" background, costs on-target are "yellow", and costs above target are "red".

VB should make it easier. Good luck.

Register to Reply
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
Good Answers: 96
#6
In reply to #4

Re: Excel / Visual Basic Cell Fill Color

07/29/2010 11:47 PM

Hi MJB,

Yes, I have done the same in conditional formatting, but what I am trying to do is create a specific RGB color in a cell from the specific numerical RGB values in other cells (ie. I have a numerical color reference so I want to SEE that color) ... so far, I haven't quite figured it out, but I am getting closer.

Thanks, and kind regards ...

__________________
"Just when I had all the answers, they changed all the questions"
Register to Reply
Guru
Engineering Fields - Electrical Engineering - Analog and Digital Circuit Design Engineering Fields - Electromechanical Engineering - Transformers, Motors & Drives, EM Launchers Engineering Fields - Engineering Physics - Applied Electrical, Optical, and Mechanical

Join Date: Jan 2008
Location: NY
Posts: 1208
Good Answers: 119
#8
In reply to #6

Re: Excel / Visual Basic Cell Fill Color

07/30/2010 12:14 AM

I think you may be attempting something that requires VBA.

http://www.cpearson.com/excel/colors.aspx

I've done what you are proposing in an HTML file, but not in a spreadsheet.

http://www.w3schools.com/html/html_colornames.asp

Best wishes.

Register to Reply
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
Good Answers: 96
#10
In reply to #8

Re: Excel / Visual Basic Cell Fill Color

07/30/2010 12:28 AM

Hi,

Yes, I'm pretty sure it will take some VBA programming. I am getting close to a solution, but code writing is not my skill, so it will take some time to figure out.

Thanks for your help, and kind regards ...

__________________
"Just when I had all the answers, they changed all the questions"
Register to Reply
Guru
United Kingdom - Member - Not a New Member Hobbies - Musician - New Member Hobbies - Fishing - New Member

Join Date: May 2006
Location: Reading, Berkshire, UK. Going under cover.
Posts: 9684
Good Answers: 467
#2

Re: Excel / Visual Basic Cell Fill Color

07/29/2010 6:28 AM

Here's a snippet from the VBA help for the interior property of a selection:

' Create a yellow interior for cell A5.
Range("A5").Select
Selection.Interior.ColorIndex = 6
Selection.Interior.Pattern = xlSolid
Selection.Interior.PatternColorIndex = xlAutomatic

(I removed the 'with ... end with' for clarity).

__________________
"Love justice, you who rule the world" - Dante Alighieri
Register to Reply
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
Good Answers: 96
#5
In reply to #2

Re: Excel / Visual Basic Cell Fill Color

07/29/2010 11:43 PM

Thanks John,

This gets me closer, but not there yet ... I did some searching based on your lead and found there is a way to make a specific RGB call out, but still working on the "HOW TO" part of it ... VBA programming is not my skill, so it take a little time to understand it all.

Kind regards ...

__________________
"Just when I had all the answers, they changed all the questions"
Register to Reply
Guru
Engineering Fields - Electromechanical Engineering - Technical Services Manager Canada - Member - Army brat Popular Science - Cosmology - What is Time and what is Energy? Technical Fields - Architecture - Draftsperson Hobbies - RC Aircraft - New Member

Join Date: Sep 2006
Location: Clive, Alberta, Canada
Posts: 5907
Good Answers: 204
#7

Re: Excel / Visual Basic Cell Fill Color

07/30/2010 12:04 AM
Register to Reply
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
Good Answers: 96
#9
In reply to #7

Re: Excel / Visual Basic Cell Fill Color

07/30/2010 12:22 AM

Oh yea, Chris ... like you want me to read ALL those threads

Actually I just did ... no direct help yet

But, as always, thanks for your help

Kind regards ...

(just wait 'til I see another post on pyramids or n-machines )

__________________
"Just when I had all the answers, they changed all the questions"
Register to Reply Off Topic (Score 5)
Guru
Engineering Fields - Electromechanical Engineering - Technical Services Manager Canada - Member - Army brat Popular Science - Cosmology - What is Time and what is Energy? Technical Fields - Architecture - Draftsperson Hobbies - RC Aircraft - New Member

Join Date: Sep 2006
Location: Clive, Alberta, Canada
Posts: 5907
Good Answers: 204
#11

Re: Excel / Visual Basic Cell Fill Color

07/30/2010 12:31 AM

I think VBA is probably the way too.... which leads to several questions.

When will the sheet be updated or triggered. (ie. Upon changing the data in the source cells?, or is a button push okay?)

What is the overall intent of the app... why are you wanting to use excel?

Chris

Register to Reply
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
Good Answers: 96
#13
In reply to #11

Re: Excel / Visual Basic Cell Fill Color

07/30/2010 1:55 AM

Here it is in a nut-shell ...

We have a reference color standard, measured in L, a, b ... we have a sample that we also measure.

In this form, I calculate the 'E' which represents a 'tolerance value' for the color, then I am converting to RGB so I can handle the data.

What I want is to be able to automatically (button push is okay) display the colors (yes, I know the colors on the monitor are not 100% accurate) as a visual reference between the two. (what I am showing here is manually done using the 'fill' icon).

__________________
"Just when I had all the answers, they changed all the questions"
Register to Reply
Anonymous Poster
#12

Re: Excel / Visual Basic Cell Fill Color

07/30/2010 1:36 AM

The following code will change the interior fill color of the selected cell to a desired color based on RGB values.

Sub Set_cell_color()

Dim R, G, B As Integer
R = 63 ' Enter the desired red value Here
G = 197 ' Enter the desired green value here
B = 133 ' Enter the desired blue value here
With Selection.Interior
.Pattern = xlSolid
.Color = RGB(R, G, B)
' Uses the RGB Function
End With

End Sub

' Or you can ask for user input

Sub Ask_cell_color()
Dim R, G, B As Integer

R = InputBox("Enter Red value.")
G = InputBox("Enter Green value.")
B = InputBox("Enter Blue Value.")

With Selection.Interior
.Pattern = xlSolid
.Color = RGB(R, G, B)
' Uses the RGB Function
End With


End Sub

The last example wil return an error if the user does not input a integer between 0 and 255 inclusive.

Register to Reply Score 1 for Good Answer
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
Good Answers: 96
#14
In reply to #12

Re: Excel / Visual Basic Cell Fill Color

07/30/2010 1:56 AM

Hello GUEST ,

This looks very promising ... I'll need a little time to 'play' with it.

Thanks, and kind regards ...

__________________
"Just when I had all the answers, they changed all the questions"
Register to Reply
Guru
Engineering Fields - Electromechanical Engineering - Technical Services Manager Canada - Member - Army brat Popular Science - Cosmology - What is Time and what is Energy? Technical Fields - Architecture - Draftsperson Hobbies - RC Aircraft - New Member

Join Date: Sep 2006
Location: Clive, Alberta, Canada
Posts: 5907
Good Answers: 204
#15
In reply to #14

Re: Excel / Visual Basic Cell Fill Color

08/01/2010 2:55 PM

I got basically the same thing as guest to work.

I put a button on the sheet. I grouped the cells together that I want to be colored
(i had to open the 'controls toolbar' in order to put the button on)
On the same toolbar is a runtime button.. make sure it is undepressed after you make the button.. this will make the button able to launch the vba code. Press the button, and it will open a vba window for this sheet, that has no code in it... simply type the following code, with corrected references to the cells the r,g,b values are in, and it should work. (or you can use mine as is. If you change the location of the cells, you have to change where vba looks for the values)

Private Sub CommandButton1_Click()

Dim r1, g1, b1 As Integer
Dim r2, g2, b2 As Integer

r1 = Sheet1.Cells(13, 2)
g1 = Sheet1.Cells(13, 3)
b1 = Sheet1.Cells(13, 4)

r2 = Sheet1.Cells(13, 6)
g2 = Sheet1.Cells(13, 7)
b2 = Sheet1.Cells(13, 8)

Sheet1.Cells(15, 2).Interior.Color = RGB(r1, g1, b1)
Sheet1.Cells(15, 6).Interior.Color = RGB(r2, g2, b2)

End Sub

I don't know how you are calculating the other values.. so drop a line if you have more questions.

Chris.

Register to Reply Score 1 for Good Answer
2
Guru
Engineering Fields - Electromechanical Engineering - Technical Services Manager Canada - Member - Army brat Popular Science - Cosmology - What is Time and what is Energy? Technical Fields - Architecture - Draftsperson Hobbies - RC Aircraft - New Member

Join Date: Sep 2006
Location: Clive, Alberta, Canada
Posts: 5907
Good Answers: 204
#16
In reply to #15

Re: Excel / Visual Basic Cell Fill Color

08/01/2010 3:58 PM

Here is a further mod.. using spinbuttons... If you send me your email on a pm, I'll send the file. I put limits in, (0-255) when you use the spinbutton, you don't have to press the 'set color' button. if you enter the colors manually, you do.. (but I think that could be fixed too. cells do have events that vba can respond to,)

Private Sub SpinButton1_SpinDown()
With Sheet1
If .Cells(13, 6) < 1 Then Exit Sub
.Cells(13, 6) = .Cells(13, 6) - 1
End With
Call CommandButton1_Click
End Sub
Private Sub SpinButton1_SpinUp()
With Sheet1
If .Cells(13, 6) > 254 Then Exit Sub
.Cells(13, 6) = .Cells(13, 6) + 1
End With
Call CommandButton1_Click
End Sub
Private Sub SpinButton2_SpinDown()
With Sheet1
If .Cells(13, 8) < 1 Then Exit Sub
.Cells(13, 8) = .Cells(13, 8) - 1
End With
Call CommandButton1_Click
End Sub
Private Sub SpinButton2_SpinUp()
With Sheet1
If .Cells(13, 8) > 254 Then Exit Sub
.Cells(13, 8) = .Cells(13, 8) + 1
End With
Call CommandButton1_Click
End Sub
Private Sub SpinButton3_SpinDown()
With Sheet1
If .Cells(13, 10) < 1 Then Exit Sub
.Cells(13, 10) = .Cells(13, 10) - 1
End With
Call CommandButton1_Click
End Sub
Private Sub SpinButton3_SpinUp()
With Sheet1
If .Cells(13, 10) > 254 Then Exit Sub
.Cells(13, 10) = .Cells(13, 10) + 1
End With
Call CommandButton1_Click
End Sub

Register to Reply Good Answer (Score 2)
Guru
United Kingdom - Member - Not a New Member Hobbies - Musician - New Member Hobbies - Fishing - New Member

Join Date: May 2006
Location: Reading, Berkshire, UK. Going under cover.
Posts: 9684
Good Answers: 467
#17
In reply to #16

Re: Excel / Visual Basic Cell Fill Color

08/01/2010 4:45 PM

Way to go, Chris!

__________________
"Love justice, you who rule the world" - Dante Alighieri
Register to Reply Off Topic (Score 5)
Guru
Engineering Fields - Electromechanical Engineering - Technical Services Manager Canada - Member - Army brat Popular Science - Cosmology - What is Time and what is Energy? Technical Fields - Architecture - Draftsperson Hobbies - RC Aircraft - New Member

Join Date: Sep 2006
Location: Clive, Alberta, Canada
Posts: 5907
Good Answers: 204
#18
In reply to #17

Re: Excel / Visual Basic Cell Fill Color

08/01/2010 5:33 PM

thanks.. something still doesn't feel right with the RGB color that results... so I will have to get some confirmation... we'll see.

Chris

Register to Reply Off Topic (Score 5)
Guru
Engineering Fields - Electrical Engineering - Analog and Digital Circuit Design Engineering Fields - Electromechanical Engineering - Transformers, Motors & Drives, EM Launchers Engineering Fields - Engineering Physics - Applied Electrical, Optical, and Mechanical

Join Date: Jan 2008
Location: NY
Posts: 1208
Good Answers: 119
#19
In reply to #18

Re: Excel / Visual Basic Cell Fill Color

08/01/2010 9:13 PM

Chris,

You are correct, something seems a bit off.

218,22,212 appears close to the magenta shade shown
111,44,256 should be 111,44,255 as 256 is out of range
111,44,255 appears to be close to the blue shown, but it looks tad more "purply" on my screen (which is NOT color calibrated)
255,255,255 is obviously WHITE and not the magenta in your clip.

Register to Reply Off Topic (Score 5)
Guru
Engineering Fields - Electromechanical Engineering - Technical Services Manager Canada - Member - Army brat Popular Science - Cosmology - What is Time and what is Energy? Technical Fields - Architecture - Draftsperson Hobbies - RC Aircraft - New Member

Join Date: Sep 2006
Location: Clive, Alberta, Canada
Posts: 5907
Good Answers: 204
#21
In reply to #19

Re: Excel / Visual Basic Cell Fill Color

08/01/2010 9:51 PM

this site seems to suggest that the code will pick the closest of excel's 56 native colors.. I'm not sure if that is for the "Cells.Interior.Color" or "Cells.Interior.ColorIndex" which I didn't use and didn't get to work. I'll keep looking.

Chris

Register to Reply
Guru
Engineering Fields - Electromechanical Engineering - Technical Services Manager Canada - Member - Army brat Popular Science - Cosmology - What is Time and what is Energy? Technical Fields - Architecture - Draftsperson Hobbies - RC Aircraft - New Member

Join Date: Sep 2006
Location: Clive, Alberta, Canada
Posts: 5907
Good Answers: 204
#22
In reply to #21

Re: Excel / Visual Basic Cell Fill Color

08/01/2010 10:22 PM

the problem might be that I'm using excel 2002.

Register to Reply
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
Good Answers: 96
#20
In reply to #15

Re: Excel / Visual Basic Cell Fill Color

08/01/2010 9:26 PM

WOW!!!

Chris, if I could award you 1000 GA's I'd do it ... you really solved the problem for me.

Here is what I ended up with:

And here is the version of your code that made it happen:

Sub Set_Colors()
'
' Set_Colors Macro
'

Dim r, g, b As Integer
Dim r1, g1, b1 As Integer
Dim r2, g2, b2 As Integer
Dim r3, g3, b3 As Integer
Dim r4, g4, b4 As Integer
Dim r5, g5, b5 As Integer
Dim r6, g6, b6 As Integer
Dim r7, g7, b7 As Integer
Dim r8, g8, b8 As Integer

r = Sheet2.Cells(28, 2)
g = Sheet2.Cells(28, 3)
b = Sheet2.Cells(28, 4)

r1 = Sheet2.Cells(28, 6)
g1 = Sheet2.Cells(28, 7)
b1 = Sheet2.Cells(28, 8)

r2 = Sheet2.Cells(28, 10)
g2 = Sheet2.Cells(28, 11)
b2 = Sheet2.Cells(28, 12)

r3 = Sheet2.Cells(63, 2)
g3 = Sheet2.Cells(63, 3)
b3 = Sheet2.Cells(63, 4)

r4 = Sheet2.Cells(63, 6)
g4 = Sheet2.Cells(63, 7)
b4 = Sheet2.Cells(63, 8)

r5 = Sheet2.Cells(63, 10)
g5 = Sheet2.Cells(63, 11)
b5 = Sheet2.Cells(63, 12)

r6 = Sheet2.Cells(98, 2)
g6 = Sheet2.Cells(98, 3)
b6 = Sheet2.Cells(98, 4)

r7 = Sheet2.Cells(98, 6)
g7 = Sheet2.Cells(98, 7)
b7 = Sheet2.Cells(98, 8)

r8 = Sheet2.Cells(98, 10)
g8 = Sheet2.Cells(98, 11)
b8 = Sheet2.Cells(98, 12)

Sheet2.Cells(30, 2).Interior.Color = RGB(r, g, b)
Sheet2.Cells(33, 15).Interior.Color = RGB(r, g, b)
Sheet2.Cells(30, 6).Interior.Color = RGB(r1, g1, b1)
Sheet2.Cells(30, 14).Interior.Color = RGB(r1, g1, b1)
Sheet2.Cells(30, 10).Interior.Color = RGB(r2, g2, b2)
Sheet2.Cells(30, 15).Interior.Color = RGB(r2, g2, b2)
Sheet2.Cells(65, 2).Interior.Color = RGB(r3, g3, b3)
Sheet2.Cells(30, 16).Interior.Color = RGB(r3, g3, b3)
Sheet2.Cells(65, 6).Interior.Color = RGB(r4, g4, b4)
Sheet2.Cells(33, 14).Interior.Color = RGB(r4, g4, b4)
Sheet2.Cells(65, 10).Interior.Color = RGB(r5, g5, b5)
Sheet2.Cells(33, 16).Interior.Color = RGB(r5, g5, b5)
Sheet2.Cells(100, 2).Interior.Color = RGB(r6, g6, b6)
Sheet2.Cells(36, 14).Interior.Color = RGB(r6, g6, b6)
Sheet2.Cells(100, 6).Interior.Color = RGB(r7, g7, b7)
Sheet2.Cells(36, 15).Interior.Color = RGB(r7, g7, b7)
Sheet2.Cells(100, 10).Interior.Color = RGB(r8, g8, b8)
Sheet2.Cells(36, 16).Interior.Color = RGB(r8, g8, b8)

End Sub

thank you, Thank You, THANK YOU ... (and to everyone else for your great help)

Kind regards ...

__________________
"Just when I had all the answers, they changed all the questions"
Register to Reply
Guru
Engineering Fields - Electrical Engineering - Analog and Digital Circuit Design Engineering Fields - Electromechanical Engineering - Transformers, Motors & Drives, EM Launchers Engineering Fields - Engineering Physics - Applied Electrical, Optical, and Mechanical

Join Date: Jan 2008
Location: NY
Posts: 1208
Good Answers: 119
#23
In reply to #20

Re: Excel / Visual Basic Cell Fill Color

08/01/2010 10:35 PM

Looks like you have your answer.

I can't be certain due to image resolution, but you appear to have some RGB values outside the range of 0-255. This could be a problem depending on how Excel/VB interprets these values.

Register to Reply
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
Good Answers: 96
#24
In reply to #23

Re: Excel / Visual Basic Cell Fill Color

08/01/2010 10:50 PM

Hi MJB,

Yes, the reverse conversion to RGB doesn't have any error trapping for 'out of range' issues, aside from showing NUM instead of a value ... at least it does that.

It gets complicated, because calculating from a "color space" reference doesn't inherently offer any good solutions ... for instance, in L a b, if I change the value of 'L' by some amount, it then determines the amount that a, or b can change ... it's parametric in a very 'nasty' way ... still working on that part .

Anyway, thanks for your great help.

Kind regards ...

__________________
"Just when I had all the answers, they changed all the questions"
Register to Reply
Guru
Engineering Fields - Electromechanical Engineering - Technical Services Manager Canada - Member - Army brat Popular Science - Cosmology - What is Time and what is Energy? Technical Fields - Architecture - Draftsperson Hobbies - RC Aircraft - New Member

Join Date: Sep 2006
Location: Clive, Alberta, Canada
Posts: 5907
Good Answers: 204
#25
In reply to #24

Re: Excel / Visual Basic Cell Fill Color

08/01/2010 11:03 PM

Perhaps you can find some lookup tables that could help... like here

I downloaded the photoshop zip file.. the Tif file, while huge (48mb) is good. and there are other color schemes there.

Chris

Register to Reply
Active Contributor

Join Date: Dec 2007
Location: London, England
Posts: 10
Good Answers: 1
#26

Re: Excel / Visual Basic Cell Fill Color

08/02/2010 4:40 AM

Hi, is this what you meant?

The preview of the image was a bit blured so the formula is =$a1=4 or 5 etc.

If it is, bear in mind that if the spread sheet page is very large, 1000s of lines it will slow up other features like filters.

I like the look of the VBA solution myself, it is more permanant.

Paulus

Register to Reply
Anonymous Poster
#27

Re: Excel / Visual Basic Cell Fill Color

08/02/2010 1:38 PM

Looks like Chris has helped solve your problem... but should we really have to become programmers to accomplish things like this? Seems like MS should have included more capabilities like this "right out of the box", so to speak. I've also used "Conditional Formatting" but it is rather limited. Nice to see a discussion and combined efforts to find a solution to a problem that I'm sure more than one reader here has wanted to understand better.

Register to Reply
Guru
United Kingdom - Member - Not a New Member Hobbies - Musician - New Member Hobbies - Fishing - New Member

Join Date: May 2006
Location: Reading, Berkshire, UK. Going under cover.
Posts: 9684
Good Answers: 467
#28
In reply to #27

Re: Excel / Visual Basic Cell Fill Color

08/02/2010 4:52 PM

Much as I'm loath to say anything in support of MS - they have to stop somewhere. The OS is included in the cost of a PC (assuming you go that way).

Admittedly Office (with Word, Excel, Access, Publisher, Outlook etc) costs a bit more on top, but if you think how many man-years have gone into it - it's hard (IMHO) to say that they should've done that much more for the cost.

Even Mighty MS (we are not worthy) can't please everyone. You want to do fancy stuff with (R,G,B) in spreadsheet cells, then maybe you have to do a bit of work yourself. Or ask a friend. Or go elsewhere - maybe there's an Open Office or whatever that can do more? (I honestly don't know).

__________________
"Love justice, you who rule the world" - Dante Alighieri
Register to Reply
Guru
Safety - Hazmat - New Member Safety - ESD - New Member Engineering Fields - Transportation Engineering - New Member Popular Science - Evolution - New Member Technical Fields - Procurement - New Member Hobbies - Target Shooting - New Member Popular Science - Cosmology - New Member Engineering Fields - Architectural Engineering - New Member Technical Fields - Marketing/Advertising - New Member Engineering Fields - Food Process Engineering - New Member

Join Date: Dec 2005
Location: Mariposa Ca
Posts: 5800
Good Answers: 114
#29
In reply to #28

Re: Excel / Visual Basic Cell Fill Color

08/03/2010 12:46 AM

If you really want to go to higher levels of functionality, look at Access, which will do everything excel will do & very much more.

you can protect your database by limiting access to it while having a much more extensive set of VB commands built in. automatically generate reports based on queries. Input can be controlled by using forms for the users to input data with

the ability to cross reference multiple databases [existing spreadsheets also] in ways that would take months with excel, in minutes with access

just like everything else there is a learning curve

the most important rule is each cell may only have one data point, a date for example is 3 data points day, month, year...

Register to Reply
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
Good Answers: 96
#30
In reply to #29

Re: Excel / Visual Basic Cell Fill Color

08/03/2010 3:09 AM

Hi Garthh,

I have enjoyed Excel for many years, first loving it for being able to do simultaneous calculations. But, as this post proves, there is a lot to learn. Just when I think I am "pretty good", I only realize there are several more layers that can be reached.

Access is something I have almost never used, but you give me reason to at least explore. Excel is a database, but limited (at least in the routine cases) to only two dimensions. A real database has "unlimited" dimensions ...

My "problem du jour" is solved, but you have given me another continent to explore.

Kind regards ...

__________________
"Just when I had all the answers, they changed all the questions"
Register to Reply
Active Contributor

Join Date: Dec 2007
Location: London, England
Posts: 10
Good Answers: 1
#31
In reply to #30

Re: Excel / Visual Basic Cell Fill Color

08/03/2010 4:01 AM

Hi Dcad,

I totally agree, I think Excel is wonderful, even though a proper data base would be better, I have difficulty getting my mind around Access, probably fear of the unknown!

Going back to your original post, you can use conditional formatting for formatting cells using data in other cells, see my post No 27 above. It does take a bit of working out to get it right, but it is still fairly versatile. If my post above doesn't make sense , please let me know.

Regards

Paulus

Register to Reply
Guru

Join Date: Aug 2007
Location: Columbia City, Indiana, USA
Posts: 832
Good Answers: 96
#32
In reply to #31

Re: Excel / Visual Basic Cell Fill Color

08/03/2010 6:24 AM

Hi Paulus,

FIRST, I should welcome you to CR4 ... a good playground of good information mixed with some fun along the way. We do get a little 'testy' now and then, but, hey, that's just human nature. Glad you're here to help us out.

SECONDLY, sorry not to respond to your response more quickly ... I've been a little buried these past few days, so responses have been here and there ... I seemed to just have "jumped over" yours ... I've sorry for that.

The conditional formatting is still an option, and your suggestion is very good ... just that I haven't had a moment to try that one yet ... I already had a start on the VBA solution, so that was easy to implement. Still, I want to try your idea, because the next project will demand the same for hundreds of RBG references ... I really don't want to write that much code, so to be able to copy and past formats would be much easier ... if it works .

Thanks for your help, and kind regards ...

__________________
"Just when I had all the answers, they changed all the questions"
Register to Reply
Register to Reply 32 comments
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be Good Answers:

These comments received enough positive ratings to make them "good answers".

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
Copy to Clipboard

Users who posted comments:

Anonymous Poster (2); chrisg288 (8); DCaD (11); Garthh (1); JohnDG (3); mjb1962853 (4); Paulus (2); The Prof (1)

Previous in Forum: W200 Wireless LAN Settings of EVO N410c Notebook   Next in Forum: Push, Pull, and...?

Advertisement