Consulting

Results 1 to 6 of 6

Thread: Conditional Formatting of cells

  1. #1

    Conditional Formatting of cells

    Hi,
    I want to apply conditional formatting to cells in my worksheet.
    If the value in the cell is "A", i want to format it to "ALL", if it is "R" i want to format it to "READ" and if it is "U" i want to format it to "UPDATE".

    Can someone help me in doing it please?

    Regards
    Harsha

  2. #2
    Conditional formatting does NOT change the content the cell displays, it can only change things like the borders and the background color of the cell.
    You can have an additional column with a formula:

    =IF(A1="A","ALL",IF(A1="R","READ",IF(A1=U","UPDATE","What if none of these apply???")))
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    I've got a similar problem but doing color formatting. I have a spreadsheet that has a series of names in column A. Column B, C, and D represent various attributes and are either "Yes" or "No". So how do I change the color of cells in column A as follows:

    If B, C, and D are all Yes, A is green
    If B, C, and D are all No, A is red
    If B, C, and D have any combination of Yes and No, A is yellow.

  4. #4
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    How about a sheet change event?

    [VBA]Option Explicit
    Option Compare Text
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    For Each cel In Range("A1:A59").Cells
    If cel.Offset(0, 1).Value = "yes" And cel.Offset(0, 2).Value = "yes" And cel.Offset(0, 3).Value = "yes" Then
    cel.Interior.ColorIndex = 4
    ElseIf cel.Offset(0, 1).Value = "no" And cel.Offset(0, 2).Value = "no" And cel.Offset(0, 3).Value = "no" Then
    cel.Interior.ColorIndex = 3
    ElseIf cel.Offset(0, 1).Value = "no" Or cel.Offset(0, 2).Value = "no" Or cel.Offset(0, 3).Value = "no" Then
    cel.Interior.ColorIndex = 6
    Else 'default conditions
    cel.Interior.ColorIndex = 0
    End If
    ' End If
    Next
    End Sub
    [/VBA]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5
    You can of course do this without VBA too.

    Set up CF as follows:

    Rule 1: Formula Is, =AND(LOWER(A2)="yes",LOWER(B2)="yes",LOWER(C2)="yes")
    Color 1: Green
    Rule 2: Formula Is, =OR(LOWER(A2)="yes",LOWER(B2)="yes",LOWER(C2)="yes")
    Color 2: Yellow
    Rule 1: Formula Is, =AND(LOWER(A2)<>"yes",LOWER(B2)<>"yes",LOWER(C2)<>"yes")
    Color 1: Red
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Simpler rules

    #1: =COUNTIF(A2:C2,"yes")=3
    #2: =COUNTIF(A2:C2,"yes")>0
    #3: =COUNTIF(A2:C2,"yes")=0

    If using Excel 2007, ensure that you tick the STop If True box, otherwise #'2 can override #1.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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