MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional font formatting based on whether another cell is bold


Posted by Moe on October 26, 2001 9:30 PM

I'm having trouble, and my limited experience with Excel is not helping matters any. I need to have the text in all cells in a row automagically change to bold or not based on one whether one cell's text in that same row is bold or not, and also make the row not bold if the referred cell is empty. I'd prefer to have it change by itself instead of having to select the row and click bold, as there are many rows to do. How do I do this?


Posted by Aladin Akyurek on October 26, 2001 10:42 PM

What is the criterion or condition under whch the referred cell is bold or not?

Posted by Moe on October 26, 2001 11:06 PM

Cell A2 is text input from user. If A2 input text is bold, then remaining cells in row must change to bold. If A2 input text is not bold, then remaining cells must change/remain not bold. If A2 is empty, then remaining cells must change/remain not bold. That's about it.

Posted by Aladin Akyurek on October 26, 2001 11:52 PM

Moe --

I'm afraid the question remains.
What is the criterion or condition the user usese when he/she makes A2 bold or not?

Posted by Moe on October 27, 2001 12:06 AM

Well, this is for a list of promos used in a TV station. Promos listed in bold are currently in rotation, and those not in bold are not in rotation, but must be kept on the list. Cells in column A with no titles must be kept in the list to stay in numerical order. The user will change or omit the text in column A depending on those conditions. I hope I've understood your question.

Posted by Aladin Akyurek on October 27, 2001 12:55 AM

> Well, this is for a list of promos used in a TV station. Promos listed in bold are currently in rotation, and those not in bold are not in rotation, but must be kept on the list. Cells in column A with no titles must be kept in the list to stay in numerical order. The user will change or omit the text in column A depending on those conditions. I hope I've understood your question.

The reason for the question is that there is no built-in function to test a cell whether it is in bold or not. If, for example, a user makes a cell bold whenever he/she enters a number bigger than $50.00, that condition/criterion (that is, >$50.00) can be used to make the entire row bold where this entry has been made. A user-defined function (UDF) is of no help, because it doesn't compute the result when the user resets the initial formatting. At t1, a2 is bold. UDF says it's bold. User changes his/her mind: he/she reformats A2 to not-bold, UDF will still report the initial formatting.

After this prelude, what is the solution? I'd suggest to keep a list of promos in rotation in a list in a separate worksheet which must be maintained (that is, updated whenever a title is no longer in rotation or a new title goes in rotation.

Such a list can be used in order to track what the user enters in A2. When the entry in A2 is in the list just suggested, the entire row (your "remaining cells") becomes bold.

The above is a bit complicated set up. If interested, let me know.

Aladin

==========

Posted by Moe on October 27, 2001 1:28 AM

I think that may be a bit overly complicated for what we need. We can just do it the old-fashioned way, by multiple row selection and clicking 'bold'. I was unsure whether there was an easy way to do it. I do appreciate the help, though. Thank you very much, Aladin.

Moe

Posted by Ivan F Moala on October 27, 2001 11:48 AM

Moe
You can do the via the sheets change event
The following code place in your worksheet code
BOLDS all cells in Column A from A3 down when
1) A2 is bold
When A2 is NOT bold or is empty it unbolds the
cells from A3 down.

Right click on your sheest tab > select View code
and cut and copy the code below........

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not [A2].Font.Bold Or [A2] = "" Then
Range("A3:A" & Rows.Count).Font.Bold = False
Else
Range("A3:A" & Rows.Count).Font.Bold = True
End If
End Sub


HTH


Ivan

Posted by Teofilo Cubillas on October 27, 2001 4:08 PM

Without VBA ....


This alternative method is not as "clean" as Ivan Moala's suggestion in that an additional column needs to be added to the worksheet and F9 needs to be pressed whenever A2's bold status is changed.
However, it may be of some academic interest :-

1. Define a name (let's say Bold) and put in the RefersTo box =GET.CELL(20,INDIRECT("RC[-1]",FALSE))
2. Insert a column between columns A & B (this new column can be always hidden if preferred)
3. In B2 enter =Bold
4. Select C2 and go to Format>ConditionalFormatting. In the FormulaIs box enter =$B$1=True . Click Format>Font>FontStyle>Bold>OK
5. Copy C2 and PasteSpecial>Formats to as many cells in row 2 as required
6. Each time the bold status of A2 is changed, press F9 to update the format of the other cells in row 2

PS
Ivan Moala's method works on Column A. I think you wanted it to work on Row 2, in which case :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not [A2].Font.Bold Or [A2] = "" Then
Range(Range("B2"), Range("IV2").End(xlToLeft)).Select
Range(Range("B2"), Range("IV2").End(xlToLeft)).Font.Bold = False
Else
Range(Range("B2"), Range("IV2").End(xlToLeft)).Font.Bold = True
End If
End Sub

Posted by Teofilo Cubillas on October 27, 2001 4:12 PM

Correction ...


This alternative method is not as "clean" as Ivan Moala's suggestion in that an additional column needs to be added to the worksheet and F9 needs to be pressed whenever A2's bold status is changed.
However, it may be of some academic interest :-

1. Define a name (let's say Bold) and put in the RefersTo box =GET.CELL(20,INDIRECT("RC[-1]",FALSE))
2. Insert a column between columns A & B (this new column can be always hidden if preferred)
3. In B2 enter =Bold
4. Select C2 and go to Format>ConditionalFormatting. In the FormulaIs box enter =$B$1=True . Click Format>Font>FontStyle>Bold>OK
5. Copy C2 and PasteSpecial>Formats to as many cells in row 2 as required
6. Each time the bold status of A2 is changed, press F9 to update the format of the other cells in row 2

PS
Ivan Moala's method works on Column A. I think you wanted it to work on Row 2, in which case :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not [A2].Font.Bold Or [A2] = "" Then
Range(Range("B2"), Range("IV2").End(xlToLeft)).Select
Range(Range("B2"), Range("IV2").End(xlToLeft)).Font.Bold = False
Else
Range(Range("B2"), Range("IV2").End(xlToLeft)).Font.Bold = True
End If
End Sub


Correction.
The revised macro sould be :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not [A2].Font.Bold Or [A2] = "" Then
Range(Range("B2"), Range("IV2").End(xlToLeft)).Font.Bold = False
Else
Range(Range("B2"), Range("IV2").End(xlToLeft)).Font.Bold = True
End If
End Sub

Posted by Ivan F Moala on October 27, 2001 7:30 PM

Re: Correction ...Thanks for that catch (NT)