Closed Thread
Results 1 to 10 of 10

Changing text color usinf a formula (NOT Conditional Formatting)

  1. #1
    John Elliott
    Guest

    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. #2
    Peo Sjoblom
    Guest

    Re: 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 Elliott@discussions.microsoft.com> wrote in message
    news:BE853A6C-DC6A-4919-BEB8-FF55683B9E95@microsoft.com...
    > 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. #3
    Biff
    Guest

    Re: 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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
    news:uKyxr4QjGHA.4660@TK2MSFTNGP03.phx.gbl...
    > 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 Elliott@discussions.microsoft.com> wrote in message
    > news:BE853A6C-DC6A-4919-BEB8-FF55683B9E95@microsoft.com...
    >> 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. #4
    John Elliott
    Guest

    Re: 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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
    > news:uKyxr4QjGHA.4660@TK2MSFTNGP03.phx.gbl...
    > > 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 Elliott@discussions.microsoft.com> wrote in message
    > > news:BE853A6C-DC6A-4919-BEB8-FF55683B9E95@microsoft.com...
    > >> 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.
    > >>

    > >
    > >

    >
    >
    >


  5. #5
    Biff
    Guest

    Re: Changing text color usinf a formula (NOT Conditional Formattin

    If none of those cells contain formulas that might return formula blanks
    then use Peo's suggestion like this:

    =COUNTA(B9,N9,Z9,AL9)

    If those cells might contain formula blanks then use this:

    =(LEN(B9)>0)+(LEN(N9)>0)+(LEN(Z9)>0)+(LEN(AL9)>0)

    Biff

    "John Elliott" <John Elliott@discussions.microsoft.com> wrote in message
    news:5A2CA2FA-A8E3-4C5E-BCD4-79055D8651BD@microsoft.com...
    > 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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
    >> news:uKyxr4QjGHA.4660@TK2MSFTNGP03.phx.gbl...
    >> > 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 Elliott@discussions.microsoft.com> wrote in
    >> > message
    >> > news:BE853A6C-DC6A-4919-BEB8-FF55683B9E95@microsoft.com...
    >> >> 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. #6
    macropod
    Guest

    Re: 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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
    news:uKyxr4QjGHA.4660@TK2MSFTNGP03.phx.gbl...
    > 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 Elliott@discussions.microsoft.com> wrote in message
    > news:BE853A6C-DC6A-4919-BEB8-FF55683B9E95@microsoft.com...
    > > 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. #7
    John Elliott
    Guest

    Re: 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. #8
    Biff
    Guest

    Re: 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" <JohnElliott@discussions.microsoft.com> wrote in message
    news:2913F8C7-BB53-4A31-85C4-30DDF8FB383F@microsoft.com...
    > 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]

    >




  9. #9
    Registered User
    Join Date
    02-06-2013
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Changing text color usinf a formula (NOT Conditional Formatting)

    I have a similar situation. I want to use formulas and not conditional formatting. I'm using lookups to compare this year's data (on another report) to last year's data and if it doesn't match, I want to put in this year's, but to mark it with a different color or size text, or underline, or bold, or just something to make it stand out. Conditional formatting won't work for this and I don't want to use a macro. I want to use a formula like the =text(vlookup(a1,b1:c13,3,false),"####"), but instead of using # to do the number format, I want code to do color or something similar. Where can we find that type of code?

  10. #10
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,746

    Re: Changing text color usinf a formula (NOT Conditional Formatting)

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1