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.
Frank FreeseAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
byundtConnect With a Mentor Commented:
In your Battleship game, would you want the font color to remain red for the rest of the game? With Conditional Formatting, the red color will vanish as soon as the competitor guesses another cell.

If you want to preserve the color, then a VBA macro would be required instead. The Worksheet_Change sub will clear the guess entry cells (B1 & B2), display the results of a guess in cell B3 and change the font color of the corresponding cell if it was a hit. The ResetFieldOfPlay sub will clear the "X" from the field of play and set font color back to black.

'Put this sub in the code pane of the worksheet being watched. It won't work at all if installed anywhere else.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, ColumnCell As Range, HitCell As Range, RowCell As Range, targ As Range
Set ColumnCell = Range("B1")
Set RowCell = Range("B2")
Set HitCell = Range("B3")
Set targ = Range("C1:E10")
If Intersect(Target, Union(RowCell, ColumnCell)) Is Nothing Then Exit Sub

On Error Resume Next
Set cel = Range(ColumnCell.Value & RowCell.Value)
On Error GoTo 0
Application.EnableEvents = False
If Not cel Is Nothing Then
    If UCase(cel.Value) = "X" Then
        cel.Font.Color = RGB(255, 0, 0)
        HitCell.Value = ColumnCell & RowCell & ": Hit"
        Union(RowCell, ColumnCell).ClearContents
    Else
        HitCell.Value = ColumnCell & RowCell & ": Miss"
        Union(RowCell, ColumnCell).ClearContents
    End If
End If
Application.EnableEvents = True
End Sub

'Put this sub in a regular module sheet
Sub ResetFieldOfPlay()
Application.EnableEvents = False
Range("B1:B3").ClearContents
Range("C1:E10").ClearContents
Range("C1:E10").Font.ColorIndex = xlAutomatic
Application.EnableEvents = True
End Sub

Open in new window

0
 
JimFiveCommented:
Have you considered using conditional formatting on the cells to set the font?
0
 
Frank FreeseAuthor Commented:
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.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Frank FreeseAuthor Commented:
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

0
 
Frank FreeseAuthor Commented:
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

0
 
Frank FreeseAuthor Commented:
I'll use conditional formatting if I can get some help with the formula, please
0
 
byundtConnect With a Mentor Commented:
A formula in a worksheet cell won't be able to change the font color. For that, you should use Conditional Formatting, with a formula criteria like:
=CONCATENATE("$",$B$2,"$",$B$1)=CELL("address",D7)

After entering the formula, click the Format button, choose the Font tab and pick your color.
0
 
Frank FreeseAuthor Commented:
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
0
 
byundtCommented:
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.
0
 
Frank FreeseAuthor Commented:
Ok...that worked. Can I bother you for one more question on this?
0
 
Frank FreeseAuthor Commented:
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!
0
 
byundtCommented:
What is the question?
0
 
Frank FreeseAuthor Commented:
Thank you so much!
Have a safe Christmas and Happy New Year.
I always learn so much from EE.
0
 
Frank FreeseAuthor Commented:
The question was preventing the X from moving. Your code does that.
0
 
byundtCommented:
If you use the code, then you don't need the Conditional Formatting.
0
 
Frank FreeseAuthor Commented:
Understood, and again many thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.