Thank you. yes, I was saying AK40 was "Supply" I couldn't remember
"AK40" and just named it. It never once crossed my mind that it was
checking the word Supply instead of the cell AK40. In cell AK40, I
have a list of values it may be- 101,
201,202,203,301,302,401,402,403,403, etc. I have different colors for
each value.
Ok, thanks for your help. You have no idea how much value it was to me.
Robert
At 2009-09-14 01:53 PM, you wrote:
>I missed your original post, but I went out and found it:
>Get value of AK40, call it "Supply"
>Select case "Supply"
> Case 101:
> DoThis("C10"),Green,White
> DoThis("J10"),Green,White
> DoThis("AB10"),Green,White
> Case 201
> DoThis("C10"),Blue,White
> DoThis("J10"),Blue,White
> DoThis("AB10"),Blue,White
> Case else
> .Value= "ERROR. UNRECOGNIZED CODE VALUE."
>End Select
>
>Sub DoThis(ThisCell As Range, BackColor,FontColor) with ThisCell
> .Interior.ColorIndex = BackColor
> .Interior.Pattern = xlSolid
> .Interior.ColorIndex = FontColor
>End With
>End Sub
>
>
>
>
>
>wow.. to begin at the beginning....
>
>Are you saying that "Supply" is the NAME of the
>
>cell AK40???
>
>
>
>or are you saying you're storing the value of the
>
>cell "AK40" in the VARIABLE called "Supply"?
>
>
>
>In your Select Case statement, what YOU are doing is
>
>testing the letters S-u-p-p-l-y and saying:
>
>If these letters ever equal 101, 201, etc..
>
>well.. it WON'T!!!
>
>
>
>let's say the answer to my first question is that you're
>
>using a VARIABLE called "Supply"...
>
>Then I would:
>
>
>
>Dim Supply
>
>Supply = Range("AK40").Value
>
>
>
>now Supply contains the value of the cell AK40.
>
>
>
>then the Case statement looks like:
>
>
>
>Select Case Supply
>
>
>
>now, as long as AK40 is 101 or 201, the Case statement should work
>
>
>
>Except for the else statement...
>
>when you use .Value
>
>the "." implies that the object you're assigning the "Value" to
>
>has been defined.. which you have not.
>
>Are you wanting the AK40 range to say "ERROR...."??
>
>
>
>then use: Range("AK40").Value = "....."
>
>
>
>next....
>
>Your function / sub call is a mess.
>
>You're defining "ThisCell" as a "Range"object.
>the strings of characters "C10", "J10", "AB10" are not "Ranges"
>they're strings of characters.
>A "Range" is an object that has all kinds of attibutes.
>(try looking at "Range Object" and select Range Object Members,
>you'll find about 170 attributes)
>
>I did some simplistic cleanup and came up with:
>
>Option Explicit
>Sub test()
> Dim Supply, stat
> Const Black = 0
> Const White = 2
> Const Red = 3
> Const Green = 4
> Const Blue = 5
> Const Yellow = 6
> Const Magenta = 7
> Const Cyan = 8
> Supply = Range("C10").Value
> Select Case Supply
> Case 101
> stat = DoThis(Range("C10"), Green, White)
> Case 201
> stat = DoThis(Range("C10"), Blue, White)
> Case Else
> MsgBox "ERROR. UNRECOGNIZED CODE VALUE."
> End Select
>End Sub
>Function DoThis(ThisCell As Range, BackColor, FontColor)
> With ThisCell
> .Interior.ColorIndex = BackColor
> .Interior.Pattern = xlSolid
> .Font.Color = FontColor
> End With
>End Function
>
>
>You're going to have to record some macros to come up with the
>appropriate "constant values" that represent the colors.
>
>hope this gets you started.
>
>P
>
>
>From: Robert Carneal USA <carnea...@adelphia.net>
>To: excel-macros@googlegroups.com
>Sent: Monday, September 14, 2009 10:51:33 AM
>Subject: $$Excel-Macros$$ Did I do something wrong?
>
>
>Hello everyone-
>
>Thank you for allowing me to join this group. When I joined, I sent a
>post about VBA from the website. This was several days ago, and I
>have not seen the post appear yet. Was I supposed to wait X days
>before posting?
>
>I posted a VBA routine that seemed to be doing nothing- that is when
>I debug it, it runs top to bottom but does not yield any results. I
>was hoping someone might see something stupid I had done, and point
>it out. I can repost if that is all right.
>
>Thank you.
>
>Robert
>
>
>
>
>
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
