Conditional Formatting with multiple text values

[titchestiny]

Hi,

I'm trying to use conditional formatting to colour cells within a column based on the text they contain. For example if A1 = A or B or C or D colour red, if A1 = E or F or G or H colour blue, if A1 = I or J or K or L colour green.

I've tried some formulas, and while they are being excepted, the cells are not changing colour. I can create formatting rules based on a single aspect i.e. if A1 = A colour red, if A1 = B colour red, but I would have to create a lot of rules, which I want to try and avoid. Any help would be greatly appreciated.

Thanks,

Tt

p.s. I'm using Excel 2010

 

[bschwartz]

Hi,

Welcome to the boards.

Try using a formula for conditional formatting. Use formulas like:

=OR($A$1={"A","B","C","D"})
=OR($A$1={"E","F","G","H"})
=OR($A$1={"I","J","K","L"})

 

[DutchDiggy]

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=OR(A1="A";A1="B";A1="C";A1="D")[/TD]
[/TR]
</tbody>[/TABLE]


To apply the code fast set up like below in your sheet and them substitute the whole with a formula, that way you can change the values quickly and copy-paste the code in your conditional-format

[TABLE="width: 256"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]OR(A1="[/TD]
[TD]A1="[/TD]
[TD]A1="[/TD]
[TD]A1="[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]";[/TD]
[TD]";[/TD]
[TD]";[/TD]
[TD]")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]=B2&B3&B4&C2&C3&C4&D2&D3&D4&E2&E3&E4[/TD]
[/TR]
</tbody>[/TABLE]
(on copy paste it'll be the code: [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]OR(A1="A";A1="B";A1="C";A1="D")[/TD]
[/TR]
</tbody>[/TABLE]

 

[Matt Rogers]

Hi,
a vba solution?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
Select Case Target

Case "A": Target.Interior.ColorIndex = 3 'red
Case "B": Target.Interior.ColorIndex = 3
Case "C": Target.Interior.ColorIndex = 3

Case "E": Target.Interior.ColorIndex = 8 'blue
Case "F": Target.Interior.ColorIndex = 8
Case "G": Target.Interior.ColorIndex = 8

Case "I": Target.Interior.ColorIndex = 4 'green
Case "J": Target.Interior.ColorIndex = 4
Case "K": Target.Interior.ColorIndex = 4
Case "L": Target.Interior.ColorIndex = 4

Case Else
Target.Interior.ColorIndex = xlNone
End Select
End If
End Sub

 

[Weazel]

Just another option if you can put your values in some distant range

=ISNUMBER(MATCH($A$1,$J$1:$J$4,0))

so j1:j4 would contain a, b, c, and d

 

[titchestiny]

Hi everyone,

Thank you for posting. DutchDiggy your solution worked the best, though I was unsure about how to apply your quicker method. Also, commas were needed instead of semi-colons between each value.

bschwartz and Weazel thank you both for posting, however I couldn't get either formula to work. Weazel yours was accepted but the cells did not change colour; bschwartz an error message appeared when I used yours.

Matt thank you for posting, I only wish I was smart enough to apply your solution. Unfortunately, I don't know what a vba is!

Thank you all for taking the time to post. I really appreciate it; you've really helped me

Tt

 

[DutchDiggy]

You're welcome! Sorry, I sometimes forget to change the ; into , here. I've got an euro-version of excel which uses the ; (I really think it's better readable then , in a formula although... )

 

[guthrie]

Try using a formula for conditional formatting. Use formulas like:

I'm not sure how to use this - I want each cell in a range to be highlighted if it contains any of a set of text values. If I type a formula into the select by formula box(=OR(={"abc","def"})" how do I specify the current cell?

Also trying to just give a sell reference there: =OR(j26={"CSW","SCI","STC","FOR","Math","Cs0"})
and assuming the formatting would substitute "current cell at each instance, gives the error:

Error: You may not use reference operators (such as unions, intersectons, or ranges) or array constants for Conditional Formatting criteria.

 

[guthrie]

Try using a formula for conditional formatting. Use formulas like:

I'm not sure how to use this - I want each cell in a range to be highlighted if it contains any of a set of text values. If I type a formula into the select by formula box(=OR(={"abc","def"})" how do I specify the current cell?

Also trying to just give a cell reference there: =OR(j26={"CSW","SCI","STC","FOR","Math","Cs0"})
and assuming the formatting would substitute "current cell" at each instance, gives the error:

Error: You may not use reference operators (such as unions, intersections, or ranges) or array constants for Conditional Formatting criteria.

 

[dennisfry]

Hello, I too am using excel 2010 (US) and trying to highlight a column based by the text entered in the cell. I have over 100 texts that use five highlights.
I have been trying the posted formulas below with no success.

Using a blank spreadsheet, cell A1, the cell formatted as General and as text
Format cells that only contain, specific text, containing, stop if true unchecked
Format set to highlight the cell red, it never highlights the cell when A or B or C or D are entered
OR(A1="A";A1="B";A1="C";A1="D")
OR(A1="A",A1="B",A1="C",A1="D")
OR(A1="A", A1="B", A1="C", A1="D")
OR (A1="A", A1="B", A1="C", A1="D")
Can someone please help me out, I am sure there is a simple mistake being made, hopefully I have communicated this accurately.
This post box hates my keyboard LOL