locked
Change cell color when "text" in that cell changes RRS feed

  • Question

  • I want the cells to change the color depending on the "text" inside it. 

    Here is just an example what I am looking for:

    A            B          C

    1      change   color Yes

    2      want      color      No  

    Here A and B cells are drop down list (data validation list) when user selects option one and C shows "Yes" I want Green color and for "No" I want to show up "Red".

    Thank you.

     

    Wednesday, July 6, 2011 8:33 AM

Answers

  • Hareesh

    Peter and Hans have rightly suggested you to use CF. However if you want a vba solution then please have a look at this.

    Please note that this code is just a sample and you will have to modify it as per your actual requirements.

    I am assuming the following.

    There is a validation List in Cell A1 (which has 3 options A, B, C) and in C1 you would have either a 'Yes' or a 'No' and the cell that will get colored is cell C1

    What this code will do is when Cell A1 has "A" and Cell C1 has a "yes" then it will color the cell C1 Green else if the Cell A1 has "A" and Cell C1 has a "No" then it will color the cell C1 Red. This code will fire the moment you make a change in either cell A1 or C1.

     

    Private Sub Worksheet_Change(ByVal Target As Range)
     On Error GoTo Whoa
     Application.EnableEvents = False
     
     If Not Intersect(Target, Range("A1")) Is Nothing Then
      If Target.Value = "A" And Target.Offset(, 2).Value = "Yes" Then
       Range("C1").Interior.ColorIndex = 4
      ElseIf Target.Value = "A" And Target.Offset(, 2).Value = "No" Then
       Range("C1").Interior.ColorIndex = 3
      End If
     ElseIf Not Intersect(Target, Range("C1")) Is Nothing Then
      If Target.Value = "Yes" And Target.Offset(, -2).Value = "A" Then
       Range("C1").Interior.ColorIndex = 4
      ElseIf Target.Value = "No" And Target.Offset(, -2).Value = "A" Then
       Range("C1").Interior.ColorIndex = 3
      End If
     End If
    LetsContinue:
     Application.EnableEvents = True
     Exit Sub
    Whoa:
     MsgBox Err.Description
     Resume LetsContinue
    End Sub
    
    HTH

     


    Sid
    • Marked as answer by Calvin_Gao Friday, July 8, 2011 10:56 AM
    Thursday, July 7, 2011 2:01 PM
  • Thank you Sid. It helps.
    Friday, July 8, 2011 3:32 AM

All replies

  • If I follow, look at Conditional Formats
    Peter Thornton

    Wednesday, July 6, 2011 9:04 AM
  • Thank you for quick reply, I had already checked the conditional formatting but could not get it for "text"..

    Please help on achieving  the following:

    A            B          C

    1      change   color  Yes

    2      want      color      No  

    Here A and B cells are drop down list (data validation list) when user selects option one and C shows "Yes" I want Green color and for "No" I want to show up "Red".

     

    Wednesday, July 6, 2011 9:34 AM
  • You can CF format the Font (ie text) colour

    In 2007/2010
    Conditional Formatting
    Format only cells that contain
    Cell Value : equal to : Yes
    Format...
    Font
    Color:

    Slightly different in 2003 but almost the same

    Peter Thornton

    Wednesday, July 6, 2011 1:26 PM
  • PS, reading your OP again you maybe you need

    Use a formula to determine which cells to format

    with the cursor in the first cell to CF, enter the relative formula

    Peter Thornton

    Wednesday, July 6, 2011 1:29 PM
  • Thank you, I tried to do it using CF "Use a formula to determine which cells to format" but could not find the right formula, Here as you can see column C cells change the Text depending on the values in column A and/or B. Could you please do it for above example and let me know?

     

    Thank you,

    -Hareesh.


    Thursday, July 7, 2011 3:41 AM
  • Try this:

    Select A1:C2 (or the full range that you want to format).

    I will assume that the active cell within the selection is in row 1.

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...

    Click "Use a formula to determine which cells to format".

    Enter the following formula:

    =$C1="Yes"

    Click Format...

    Specify green as font color or fill color, depending on what you want.

    Click OK twice.

    Click Conditional Formatting > New Rule... again.

    Click "Use a formula to determine which cells to format".

    Enter the following formula:

    =$C1="No"

    Click Format...

    Specify red as font color or fill color, depending on what you want.

    Click OK twice.

     


    Regards, Hans Vogelaar
    • Proposed as answer by Calvin_Gao Thursday, July 7, 2011 6:05 AM
    Thursday, July 7, 2011 5:56 AM
  • Thank you, "Yes" and "No" are just example, If I want color to change for any "text" change dynamically, is it possible? as there are lot of output apart from "Yes" and "No".

     

    Thank you,

    -Hareesh.

    Thursday, July 7, 2011 9:46 AM
  • I don't quite follow what you want but maybe

    cell value is equal to =$D$1

    This way user could type a value into D1 then any cell with the CF rule will trigger if it's the same as D1

    Peter Thornton

    Thursday, July 7, 2011 10:20 AM
  • Hareesh

    Peter and Hans have rightly suggested you to use CF. However if you want a vba solution then please have a look at this.

    Please note that this code is just a sample and you will have to modify it as per your actual requirements.

    I am assuming the following.

    There is a validation List in Cell A1 (which has 3 options A, B, C) and in C1 you would have either a 'Yes' or a 'No' and the cell that will get colored is cell C1

    What this code will do is when Cell A1 has "A" and Cell C1 has a "yes" then it will color the cell C1 Green else if the Cell A1 has "A" and Cell C1 has a "No" then it will color the cell C1 Red. This code will fire the moment you make a change in either cell A1 or C1.

     

    Private Sub Worksheet_Change(ByVal Target As Range)
     On Error GoTo Whoa
     Application.EnableEvents = False
     
     If Not Intersect(Target, Range("A1")) Is Nothing Then
      If Target.Value = "A" And Target.Offset(, 2).Value = "Yes" Then
       Range("C1").Interior.ColorIndex = 4
      ElseIf Target.Value = "A" And Target.Offset(, 2).Value = "No" Then
       Range("C1").Interior.ColorIndex = 3
      End If
     ElseIf Not Intersect(Target, Range("C1")) Is Nothing Then
      If Target.Value = "Yes" And Target.Offset(, -2).Value = "A" Then
       Range("C1").Interior.ColorIndex = 4
      ElseIf Target.Value = "No" And Target.Offset(, -2).Value = "A" Then
       Range("C1").Interior.ColorIndex = 3
      End If
     End If
    LetsContinue:
     Application.EnableEvents = True
     Exit Sub
    Whoa:
     MsgBox Err.Description
     Resume LetsContinue
    End Sub
    
    HTH

     


    Sid
    • Marked as answer by Calvin_Gao Friday, July 8, 2011 10:56 AM
    Thursday, July 7, 2011 2:01 PM
  • Thank you Sid. It helps.
    Friday, July 8, 2011 3:32 AM
  • Glad it helped Hareesh but I guess you chose your comment as an answer :)


    Sid
    Friday, July 8, 2011 7:44 AM