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
-~----------~----~----~----~------~----~------~--~---
