Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Excel If/Then statements


(!)

Seeseman4's Avatar
Seeseman4 Seeseman4 is offline
Computer Specs
Junior Member with 6 posts.
THREAD STARTER
 
Join Date: Jan 2012
Experience: Beginner
04-Jan-2012, 02:35 PM #1
Solved: Excel If/Then statements
Hey Guys,

I'm writing a Macro for Excel that takes data from one spreadsheet and presents it neatly (and printably) in another. Part of the information is the Status of the project, which is represented by a color (Green, Red, Yellow.) The first spreadsheet contains this information, but only in black and white. The second sheet pulls this information directly from the first in order to fill in the appropriate area.

My question is whether or not it would be possible for the second sheet to display the status in the color that it should be using an if than statement. The limitation I'm hitting is that the content of the cell is not "green" or "Yellow", but "=DynamicReport!A4" because it's pulling the color from the first sheet. Any thoughts?
bomb #21's Avatar
Member with 8,263 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
04-Jan-2012, 02:57 PM #2
Can you explain "The first spreadsheet contains this information, but only in black and white."?
Seeseman4's Avatar
Seeseman4 Seeseman4 is offline
Computer Specs
Junior Member with 6 posts.
THREAD STARTER
 
Join Date: Jan 2012
Experience: Beginner
04-Jan-2012, 03:02 PM #3
Sure, I'm sorry for being unclear.

I have two tabs open in Excel. The first is the original document, which contains my data. On that sheet there is a cell with the text "red" or "green", depending on the status of the project it is referring to. On the second sheet, I am using a macro to pull the value of that cell into the second sheet. it looks like this:

Range("A9").Select
Selection.Font.Bold = False
ActiveCell.Formula = "=DynamicReport!C8"

A9 is the cell in the second sheet. As you can see, I've entered a formula into that cell that pulls information from the first sheet. That information is "red" or "green". What I meant by black and white is that the color of the font from the first sheet is only black. I cannot add color to the first sheet, and therefore I need to change the color of that cell once it appears on the second sheet.
bomb #21's Avatar
Member with 8,263 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
04-Jan-2012, 03:04 PM #4
So, for the basics, does this work for you?

Sub test()
If Range("A9") = "Red" Then
Range("A9").Interior.ColorIndex = 3
ElseIf Range("A9") = "Yellow" Then
Range("A9").Interior.ColorIndex = 6
ElseIf Range("A9") = "Green" Then
Range("A9").Interior.ColorIndex = 10
End If
End Sub

Last edited by bomb #21; 04-Jan-2012 at 03:18 PM..
Seeseman4's Avatar
Seeseman4 Seeseman4 is offline
Computer Specs
Junior Member with 6 posts.
THREAD STARTER
 
Join Date: Jan 2012
Experience: Beginner
04-Jan-2012, 03:48 PM #5
Yeah that works great. Is there a way I can use a different color for green? it's too dark to read the black text. If not it will have to do.

Thanks a lot, I really appreciate the help.
bomb #21's Avatar
Member with 8,263 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
04-Jan-2012, 03:58 PM #6
So you can see that with:

Sub test()
Select Case Sheets("DynamicReport").Range("C8")
Case "Red"
Range("A9").Interior.ColorIndex = 3
Case "Yellow"
Range("A9").Interior.ColorIndex = 6
Case "Green"
Range("A9").Interior.ColorIndex = 10
End Select
End Sub


you wouldn't even need the linking formula in the first place?

"Is there a way I can use a different color for green?"

Just colour a cell how you like, then use:

MsgBox ActiveCell.Interior.ColorIndex

to get the index.
Zack Barresse's Avatar
Computer Specs
Member with 5,419 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
04-Jan-2012, 05:03 PM #7
If you want to see all available colors, you could look at something like this. It's handy-dandy every now and then. Nothing special, but down and dirty and gets you there in a hurry.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=206

HTH
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
excel, macro

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑