if cell format

  • Thread starter Thread starter ksnapp
  • Start date Start date
K

ksnapp

Hi,

would some be kind enough to show me how to word an if then statemen
for formating? Here is the question in regular people talk.

if the cell A1 is bold return true in not then false

thank
 
Hi
you need VBA for this. Try the following user defined function:

Public Function is_bold(rng As Range) As Boolean
application.volatile
is_bold = rng.Font.Bold
End Function

us this like
=IS_BOLD(A1)
 
Couple of things the OP should be aware of:

1) Even with the Application.Volatile statement, this function is never
guaranteed to be correct, since bolding or unbolding a cell doesn't
trigger a calculation.

2) This works well for 1 cell. It appears to work well for multiple
cells, in that if all are bold, it returns TRUE, if none are bold, it
returns FALSE. However, if there's a mix, rng.Font.Bold evaluates to
Null, and, since the Function is declared as Boolean, it returns the
#VALUE! error. I'd expect it to return False (i.e., implied AND rather
than implied OR)

One way around that:

Public Function Is_Bold(rng As Range) As Boolean
Dim vTemp As Variant
Application.Volatile
vTemp = rng.Font.Bold
If IsNull(vTemp) Then vTemp = False
Is_Bold = vTemp
End Function
 
Conditional Formatting can be done in VBA as Kabel showed.

A simpler approach is to use the User Interface

Select a cell to format.
Format > Conditional Formatting...
In the Condition 1 box:
You can choose "Cell Value Is" or "Formula is". To set the condition
you need to test that a value in another cell, then use "Formula Is"

If you choose "Cell Value Is".
Second box: choose a test condition like "between"
Third and Fourth boxes: enter either hard coded numbers or cell
references for the bounds of the condition.
If you choose "Formula Is", then you can set the cell format based upon
the value of a different cell. If the formula is in A8 and you want to
test C8 to see if it has an error in it, you can enter
=ISERROR($C8)

Once the condition has been defined:
click the Format... button.
there is a Font tab where you can choose BOLD and ITALIC
combinations and Font Color and underline treatment.
there is a Border Tab
There is a Patterns Tab where you can change the interior
(background) color.

All that for one condition. If you want an "ElseIF" condition, press the
Add >> button to create a Condition 2 or a Condition 3. 3 is the limit.

Conditional Formats are copied and pasted along with all other Formats.
So you can copy a Conditional Format cell and paste it many places. The
Cell References honor the absolute or relative addresses, just like any
other cell formula.

Stephen Rasey
WiserWays, LLC
Houston, TX
 
Back
Top