Solved

Building upon an IF statement

Posted on 2013-12-20
16
234 Views
Last Modified: 2013-12-21
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
Comment
Question by:Frank Freese
  • 10
  • 5
16 Comments
 
LVL 15

Expert Comment

by:JimFive
Comment Utility
Have you considered using conditional formatting on the cells to set the font?
0
 

Author Comment

by:Frank Freese
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
I'll use conditional formatting if I can get some help with the formula, please
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 500 total points
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
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
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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
 

Author Comment

by:Frank Freese
Comment Utility
Ok...that worked. Can I bother you for one more question on this?
0
 

Author Comment

by:Frank Freese
Comment Utility
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
 
LVL 80

Expert Comment

by:byundt
Comment Utility
What is the question?
0
 

Author Closing Comment

by:Frank Freese
Comment Utility
Thank you so much!
Have a safe Christmas and Happy New Year.
I always learn so much from EE.
0
 

Author Comment

by:Frank Freese
Comment Utility
The question was preventing the X from moving. Your code does that.
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
If you use the code, then you don't need the Conditional Formatting.
0
 

Author Comment

by:Frank Freese
Comment Utility
Understood, and again many thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now