Link to home
Start Free TrialLog in
Avatar of Frank Freese
Frank FreeseFlag for United States of America

asked on

Building upon an IF statement

Folks,
The below code works OK. I would like to add a little bit more to this (building myself a Battleship program for my son).
If  there is an X at D7, for example, I would like to change the font at D7 to red as a part of this IF statement. Right now the range where the battleships are have some X's for the battleship's location (font color is black x's) and the forground range is also black.

=IF(INDIRECT(B2&B1)="X","Hit", "")

Open in new window


Thanks for any help here.
Avatar of JimFive
JimFive
Flag of United States of America image

Have you considered using conditional formatting on the cells to set the font?
Avatar of Frank Freese

ASKER

I have considered conditional formatting. I have been battling the IF statement and being poor with nested if's I haven't given up on that option.
Folks,
I've tried this in my feeble attempt to meet my objective to no avail.

=IF(INDIRECT(B2&B1)="X","Hit",IF(B3="Hit",Range("B2&B1").Font.Color=RGB(255,0,0),B3="")))

Open in new window

And I've tried this

=IF(INDIRECT(B2&B1)="X","Hit",IF(B3="Hit",ActiveSheet.Range("B2&B1").Font.Color=RGB(255,0,0),B3="")))										

Open in new window

I'll use conditional formatting if I can get some help with the formula, please
SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I appreciate the CONCATENATE suggestion. I glad to know that one cannot change the font color in a formula.

Here's what I tried as a formula before using Conditional Formatting:
=IF(INDIRECT(B2&B1)="X",B2&B1,"")

This returned a value of D6, which has an "X". The range I select for this is C1:E10.
I then used the above statement in Conditional Formatting and formatted the cell.
I was hoping to see D6 formatted as red "X", but nothing happened. Suggestions
Please select cell D7, then create Conditional Formatting using the formula I suggested. It should work. If it does, you can copy the cell, then select C1:E10 and do a Paste Special...Formats.

If you still have trouble with applying the Conditional Formatting, please post your workbook.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok...that worked. Can I bother you for one more question on this?
You were reading my mind with the code you posted. This game is part of my youngest son's Xmas (3 yrs)  and he'll really have fun.
Fantastic!
What is the question?
Thank you so much!
Have a safe Christmas and Happy New Year.
I always learn so much from EE.
The question was preventing the X from moving. Your code does that.
If you use the code, then you don't need the Conditional Formatting.
Understood, and again many thanks!