How to apply automatic format changes to cells?

  • Thread starter Thread starter RLymburner
  • Start date Start date
R

RLymburner

Using Excel 2000, is there a way to update cell formatting
automatically after they change? That is, I can
use "format painter" to copy formatting after the fact,
but is there a method to copy the formatting on-the-fly?

The purpose is for a fantasy football draft in which on
page 1, as a player is drafted I would change the cell
formatting containing their name as "strikethrough". On
all the following pages, the linked cells would be crossed
out as well. How can this be done?

Thanks!
 
You're welcome. Btw, if you double-click on the Format Painter icon (instead
of single-clicking), you can continue the copy-formatting viz "painting"
until you hit Escape key. cheers.
 
Here's just another idea to play around with. Place your player names in
Column B. Look to place an "x" in Column A to indicate the player is
drafted.
You could do this quickly by running the following code to just double click
a cell in Column A. Place this code in the particular Sheet Module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column = 1 Then
If Target = vbNullString Then
Target = "X"
Else
Target = vbNullString
End If
End If
End Sub

Now, generate a 3rd column that contains the names that need to be "crossed
out." For example, in C1, perhaps a formula like...
=IF(A1="X",B1,"")

Give the Column C names a range name like "Drafted." For Conditional
Formatting, you could use a formula like this...
=MATCH(K1,Drafted,0)>0

Here, K1 was the cell at the top of a list of names.

If you want to Conditional Format those names that are not drafted yet (say
Bold), you could add a second condition perhaps like this...
=ISERROR(MATCH(K1,Drafted,0)=0)

Another option for the x's in Column A is to set the Font for column A to
"Wingdings", and use character 252 for a check mark.
 
Back
Top