Question 1:
Is it possible to have conditional formating based on the format of another
cell instead of the value of another cell? For example, I need A1 to show a
strikethrough, but only if A2 is formatted with a strikethrough. Is this
possible?
Question 2:
Is it possible to copy conditional formatting into other cells while
allowing Excel to logically adjust the formula to the current perameters
(much like copying and pasting a simple formula)?
My Answer 1:
Sort of possible.
If there was a standard excel worksheet function that could detect the
strikethrough font format then it would definitely be possible.
However, as far as I know there is no such a function.
You could, however, use VBA to make a User Defined Function or UDF to
detect the strikethrough format. Following is my attempt...
Public Function IsStrikeThrough(Cell As Range) As Boolean
Application.Volatile
IsStrikeThru = Cell.Font.Strikethrough
End Function
Trouble with this though is Conditional Formatting does not allow
UDFs.
The solution to that is to use the UDF in a free cell, eg B1, then in
the "Formula Is:" box on the Conditional Formatting dialog use =B1.
There is still a problem though. When the user reformats A2 to change
its strikethrough state the UDF does not calculate the new state
because formatting cells does not force the workbook to recalculate.
A "sort of" solution is to use the worksheet's SelectionChange event
procedure to detect when the user has just finished with a selection
of cells that includes A2. When excel detects that the user has
changed the range of selected cells from one including A2 to one not
including A2 the event procedure can then calculate the workbook just
in case the user had reformatted A2 while it was selected.
For this to always work properly though, two other changes need to be
included. When the workbook is closed it is essential that it not be
saved with A2 selected so that when it is reopened, if the user wants
to reformat A2 they have to select it first.
If A2 is already selected when the workbook is re-opened the
SelectionChange event will not be able to detect that the previous
selection included A2.
So, the two other changes are in the ThisWorkbook code module...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Range("A1").Select
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Range("A1").Select
End Sub
I've used A1, but it can be any cell except A2, the one whose
formatting is to be detected.
My Answer 2:
Your description of the way you want Conditional Formatting to work
sound to me like the way it does actually work. If you selected C1:C10
then used (for example) =ISNUMBER(C1) in the "Formula Is:" box, after
you have completed the Conditional formatting, if you check the
formula that excel is using in say C2 you will see =ISNUMBER(C2). If
the original formula was =ISNUMBER(C$1) though, in C2 excel would
still be using ISNUMBER(C$1).
The same is true after copy/paste, excel adjusts Conditional
Formatting formulas to suit the new cells.
Ken Johnson
