PDA

View Full Version : Conditional Formatting of cells



hputhraya
03-10-2010, 02:15 AM
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

Jan Karel Pieterse
03-10-2010, 04:41 AM
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???")))

RKramkowski
03-10-2010, 02:44 PM
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.

lucas
03-10-2010, 03:33 PM
How about a sheet change event?

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

Jan Karel Pieterse
03-10-2010, 10:11 PM
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

xld
03-11-2010, 01:50 AM
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.