Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing text color usinf a formula (NOT Conditional Formatting)
Is it possible to change the color of text using a formula, such as an IF
statement? What I have in mind is something like this (stated simply): If cells B9 or N9 or Z9 or AL9 are blank, do nothing, else color text in cell S4 red. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing text color usinf a formula (NOT Conditional Formatting)
look in help for conditional formatting
in the formula is box put =COUNTA(B9,N9,Z9,AL9)=4 click the format button and select font colour and click OK twice the above means that all 4 cells need to have data for this to happen -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "John Elliott" <John wrote in message ... Is it possible to change the color of text using a formula, such as an IF statement? What I have in mind is something like this (stated simply): If cells B9 or N9 or Z9 or AL9 are blank, do nothing, else color text in cell S4 red. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing text color usinf a formula (NOT Conditional Formatting)
=COUNTA(B9,N9,Z9,AL9)=4
That will count formula blanks. Maybe this: =(LEN(B9)0)*(LEN(N9)0)*(LEN(Z9)0)*(LEN(AL9)0) Biff "Peo Sjoblom" wrote in message ... look in help for conditional formatting in the formula is box put =COUNTA(B9,N9,Z9,AL9)=4 click the format button and select font colour and click OK twice the above means that all 4 cells need to have data for this to happen -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "John Elliott" <John wrote in message ... Is it possible to change the color of text using a formula, such as an IF statement? What I have in mind is something like this (stated simply): If cells B9 or N9 or Z9 or AL9 are blank, do nothing, else color text in cell S4 red. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing text color usinf a formula (NOT Conditional Formattin
Both your reply and Peo Sjoblom's both go some way toward solving this. But
both require ALL 4 cells to be populated. I need a way for any ONE cell of the four to be populated for the S4 cell to have its contents changed to red. Or, another way that might be even easier for me in the long ru, would be this kind of formula: IF any one of these 4 cells is not blank (B9 or N9 or Z9 or AL9), then change the text in these four cells (B8, N8, Z8 and AL8) to red. Thanks for all your help in this. I hope to have this solved soon. --John "Biff" wrote: =COUNTA(B9,N9,Z9,AL9)=4 That will count formula blanks. Maybe this: =(LEN(B9)0)*(LEN(N9)0)*(LEN(Z9)0)*(LEN(AL9)0) Biff "Peo Sjoblom" wrote in message ... look in help for conditional formatting in the formula is box put =COUNTA(B9,N9,Z9,AL9)=4 click the format button and select font colour and click OK twice the above means that all 4 cells need to have data for this to happen -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "John Elliott" <John wrote in message ... Is it possible to change the color of text using a formula, such as an IF statement? What I have in mind is something like this (stated simply): If cells B9 or N9 or Z9 or AL9 are blank, do nothing, else color text in cell S4 red. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing text color usinf a formula (NOT Conditional Formatting)
Hi Peo,
The OP wants a solution that doesn't entail conditional formatting. Perhaps an event-driven macro, such as the following, which could be put in the Workbook module (if that doesn't count as conditional formatting)? Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Dim i As Integer Dim j As Integer With ThisWorkbook.Sheets("Sheet1") If .Range("B9") = "" Or .Range("N9") = "" Or .Range("Z9") = "" _ Or .Range("AL9") = "" Then .Range("G4").Font.ColorIndex = 3 Else .Range("G4").Font.ColorIndex = 1 End If End With End Sub Cheers -- macropod [MVP - Microsoft Word] "Peo Sjoblom" wrote in message ... look in help for conditional formatting in the formula is box put =COUNTA(B9,N9,Z9,AL9)=4 click the format button and select font colour and click OK twice the above means that all 4 cells need to have data for this to happen -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "John Elliott" <John wrote in message ... Is it possible to change the color of text using a formula, such as an IF statement? What I have in mind is something like this (stated simply): If cells B9 or N9 or Z9 or AL9 are blank, do nothing, else color text in cell S4 red. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing text color usinf a formula (NOT Conditional Formattin
macropod,
I rather like the macro idea. I'll have to try it tomorrow and see how it goes. Biff, I'll keep your suggestions in mind, also. Thanks, all! "macropod" wrote: Hi Peo, The OP wants a solution that doesn't entail conditional formatting. Perhaps an event-driven macro, such as the following, which could be put in the Workbook module (if that doesn't count as conditional formatting)? Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Dim i As Integer Dim j As Integer With ThisWorkbook.Sheets("Sheet1") If .Range("B9") = "" Or .Range("N9") = "" Or .Range("Z9") = "" _ Or .Range("AL9") = "" Then .Range("G4").Font.ColorIndex = 3 Else .Range("G4").Font.ColorIndex = 1 End If End With End Sub Cheers -- macropod [MVP - Microsoft Word] |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing text color usinf a formula (NOT Conditional Formattin
Just curious, why do you NOT want to use CF?
It's easier and more efficient than an event macro. Biff "John Elliott" wrote in message ... macropod, I rather like the macro idea. I'll have to try it tomorrow and see how it goes. Biff, I'll keep your suggestions in mind, also. Thanks, all! "macropod" wrote: Hi Peo, The OP wants a solution that doesn't entail conditional formatting. Perhaps an event-driven macro, such as the following, which could be put in the Workbook module (if that doesn't count as conditional formatting)? Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Dim i As Integer Dim j As Integer With ThisWorkbook.Sheets("Sheet1") If .Range("B9") = "" Or .Range("N9") = "" Or .Range("Z9") = "" _ Or .Range("AL9") = "" Then .Range("G4").Font.ColorIndex = 3 Else .Range("G4").Font.ColorIndex = 1 End If End With End Sub Cheers -- macropod [MVP - Microsoft Word] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conflicting Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional formatting with numbers and text | Excel Worksheet Functions | |||
conditional formatting based on another cells formula result | Excel Discussion (Misc queries) | |||
Highlighting text using conditional formatting | Excel Discussion (Misc queries) | |||
Concatenation formula loses text wrap formatting | Excel Discussion (Misc queries) |