Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

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.
0
Frank Freese
Asked:
Frank Freese
  • 10
  • 5
2 Solutions
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
byundtCommented:
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
 
byundtCommented:
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
 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 10
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now