Blogger
Share this content
0
3
3263

Change a cell colour conditionally

Here's a challenge...

Via conditional formatting, or similar automation, I would like to change a CELL colour (to say green) by clicking the character in the cell to BOLD.

Thanks
Michael Ung

Replies

Please login or register to join the discussion.

avatar
By mung1
09th Dec 2008 16:03

Thanks
Richard

Thanks for your solution - it works a treat.

Mike

Thanks (0)
avatar
05th Dec 2008 11:15

One way..
Hello Michael

One way to achieve this is thru the use of an old XLM4 macro function (the following assumes you are using xl2003 or below, altho the steps will work on 2007 with minor amendments):

1. From the main Excel menu go Insert>Name>Define

2. This opens the Name dialog. Call the name you are about to create CellBold

3. In the Refers To box at the bottom of the dialog type the following and click Add/OK:

=GET.CELL(20,INDIRECT("RC",FALSE))

4. Back in the worksheet, select the cells you want this Conditional Formatting to apply to and go Format>Conditional Formatting. In the CF dialog, use the left most dropdown to switch from Value Is to Formula Is and in the formula bar to the right type:

=CellBold

5. Hit the Format button and apply your chosen CF as desired. OK out of the CF dialog and your CF is now set up!

Note that this will format an otherwise empty cell if you have applied Bold formatting to it. Should you want such cells to remain un-conditionally formatted then you could use a somewhat more complicated formula in the CF dialog:

=CellBold*(LEN(INDIRECT("RC",FALSE))>0)

Hope this helps!

Best regards

Richard
Microsoft Excel MVP

Thanks (0)
avatar
04th Dec 2008 19:45

Add this macro
You'll need to run this for each cell you want to check:


Sub FormatTest()
'
' FormatTest Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
If Selection.Font.Bold = True Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Else
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End Sub

Thanks (0)