# Thread: Help with an IF statement in Excel

1. ## Help with an IF statement in Excel

Hi folks, this my first post and I hope I am in the right place. I am using Excel in Office 2007
I have this If statement =IF(B4=0,"Market closed",(11.5*B4)). The statement works fine but I would like it to print the text string, Market closed in his case, in red.

Thanks for any help.

2. R.R.,

Welcome to the Lounge as a new poster!

1. Select the cell containing the formula.
2. On the Home Tab select Conditional Formatting.
3. Select New Rule.
4. Select Use a formula to determine which cells to format.
5. Fill in the boxes:
CondFmt.PNG
Note the formula is: =A1="Market Closed"
6. Click OK.

HTH

3. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

Redryder45 (2016-06-27)

4. I guess I left out an important piece of information. I m pretty much a beginner. I folowed the steps but nothing happened

=IF(B3=0,"Market closed",(11.5*B3)) This IF statement is in B3 is in cell B3
The date is in cell A3. A dollar amount is in B3. t he formula is in C3.
5/1/2016 \$0.00 Market closed
5/2/2016 \$25.00 \$287.50
5/3/2016 \$12.00 \$138.00
5/4/2016 \$10.00 \$115.00
5/5/2016 \$21.00 \$241.50

5. =IF(B3=0,"Market closed",(11.5*B3)) This IF statement is in B3 is in cell B3
Redryder,

Above, you are saying you have a formula in Cell B3 that references itself (circular reference).

Cell B3 =IF(B3=0,"Market closed",(11.5*B3))

You cannot do this in Excel

I think what you meant was

Cell C3 =IF(B3=0,"Market closed",(11.5*B3))

This can also be done using a user defined function

In a standard module, enter the following code:
Code:
```Public Function Market(rng As Range)
Market = IIf(rng = 0, "Market Closed", rng * 11.5)
If Market = "Market Closed" Then
Application.Caller.Font.Color = vbRed
Else:
Application.Caller.Font.Color = vbBlack
End If
End Function```
In Cell C3 enter the following formula:
cell C3 = Market(B3) then copy down

Market1.png

HTH,
Maud

6. ## The Following User Says Thank You to Maudibe For This Useful Post:

Redryder45 (2016-06-27)

7. R.R.,

Ok now I understand. If you want to do this w/o code you can use conditional formatting as follows:

1. Create a Dynamic Range Name for column C. I chose to call it MarketValue and it is defined like this:
1. In the Name Manager on the Format Tab.
2. Click New
3. Type the name in the Name: box
4. In the Scope box select the sheet name as appropriate, in the enclosed example Sheet1.
5. In the Refers to: box type the formula below. (This allows for up to 10,000 rows!)
=OFFSET(Sheet1!\$C\$3,0,0,COUNTA(Sheet1!\$C\$3:\$C\$1000 2),COUNTA(Sheet1!\$C\$3:\$C\$3))
6. Click OK.
2. Highlight the used values in Column C starting in C3.
3. Click Conditional Formatting on the Home Tab.
4. Click New Rule
5. Click Use a formula to determine which cells to format.
6. In the Edit the Rule Description: box type:
=UPPER(\$C3)="MARKET CLOSED"
7. Click the Format... button.
8. Set the format the way you want it.
9. Click OK.

You're done. As you add rows the conditional format will automatically apply to Column C and you can fill the formula in column C down as necessary. Actually the formula seems to be auto filling!

Try changing the value in Column B to zero and see what happens.

Test File: Market-RGV1.xlsx

HTH

8. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

Redryder45 (2016-06-27)

9. We are getting lost here somehow. Here is a screen shot http://screencast.com/t/2who1SWVIcZ

As you can see Cell A3 contains only the date
Cell B3 will contain a number.
Cell C3 contains the If statement. The If statement looks at cell B3 and if it sees a zero it prints Market Closed, if it sees a number greater than zero it multiplies that number times 11.5 and puts the answer in cell C3.

That all works fine there is no circular reference.

What I would like to now is not only print Market Closed but to also print it in RED.

I hope that is a better explanation this time. As I said I am a bit of a newbie at this Excel game.

Thanks for your help so far.

10. I will see if I can figure that one out. LOL

11. Hi

You can create a custom format to give you exactly what you want.
Excel custom formats are defined in four Sections, separated by semicolons:
Positive numbers; Negative Numbers; Zero Values ;Text values.
Text is only affected by custom formats when you use all four sections.
Text would use the last section.

so you could :
select your formula range and apply this custom format to that range:
\$#,##0.00;;;[Red]General

see attached file

zeddy
Day 15 in hospital

12. ## The Following User Says Thank You to zeddy For This Useful Post:

Redryder45 (2016-06-28)

13. ZEDDY: why have you been in the hospital for 15 days???

14. R.R.,

Did you check out the test file in my last post?

15. I am sorry, I couldn't get it to work but following your formula I think my workbook is set up wrong. I started playing a bit today but didn't have much time. Probbaly won't tomorrow either but I think I am beginning to see waht you are doing.
Thanks

16. I will try your suggestion too. It never hurts to have more than one solution. I hope you get out of the hospital soon. They certainly arent my favorite places.

17. Problem solved. Thank you all for the interest. I used the custom number format from Zeddy because it was the simplest for me.
I still plan to try to use RetiredGeek's answer "Create a Dynamic Range Name for column C" when I have some time.
Redryder45

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•