1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel If/Then statements

Discussion in 'Business Applications' started by Seeseman4, Jan 4, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Seeseman4

    Seeseman4 Thread Starter

    Joined:
    Jan 4, 2012
    Messages:
    6
    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?
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,269
    Can you explain "The first spreadsheet contains this information, but only in black and white."? :confused:
     
  3. Seeseman4

    Seeseman4 Thread Starter

    Joined:
    Jan 4, 2012
    Messages:
    6
    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.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,269
    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
     
  5. Seeseman4

    Seeseman4 Thread Starter

    Joined:
    Jan 4, 2012
    Messages:
    6
    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.
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,269
    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.
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
  8. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1034546