Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a way to use conditional formatting in the following manner in Excel? (For those who are curious, I am using Excel 2010 for Mac)

I want Cell B2 to be green when A2 says "John likes green apples" and B2 also says "John likes green apples", yellow if A2 says "John likes green apples" and B2 says "Bill likes blue apples", and red if A2 says "John likes green apples" and B2 says "Bill hates red oranges".

I am just wondering if there is a way to something to that effect.


share|improve this question
Just to be clear, the metric is the number/percentage of words in B2 that are found in A2? – Excellll May 14 '14 at 20:59
Anything like a percentage works. It doesn't need to be exact, I just need something relative. – user1470242 May 14 '14 at 21:21
Would VBa be acceptable or do you only want to do this with conditional formatting – Dave May 15 '14 at 13:12
As requested by @slhck I've deleted my answer that outlined how to achieve this on a PC, and posted a question and answer here to address the PC case in particular:… – Andi Mohr May 15 '14 at 15:09
VBA is acceptable. I wasn't aware that was an option, it's the result I care about. Thanks @DaveRook – user1470242 May 15 '14 at 16:13

Sure. Select cell B2 and go to Home tab > Styles group > Conditional Formatting > New Rule > Use formula to determine which cells to format

Enter this formula:

=AND(A2="John likes green apples",B2="John likes green apples")

Set the set the format to fill green.

Repeat for each additional color and rule.

share|improve this answer
So my thing is that I have a couple thousand rows, each with different text in A and B, I was hoping the conditional formatting would help me identify which sets were more alike, and I don't want to have to go one by one. Any suggestions? I appreciate the reply. – user1470242 May 15 '14 at 6:46
It sounds like you may want Fuzzy Lookup. – Calvin May 15 '14 at 7:22
Note where I say I am using Excel 2010 for Mac, I was so hoping that would work but it looks like I would need a PC. – user1470242 May 15 '14 at 7:25
Oh, I didn't realize it was Windows only. You should be able to use a fuzzy comparison implemented in VBA. I haven't tried it, but this one looks promising. – Calvin May 15 '14 at 7:39

This would definitely require VBA. The following function will return a value from 0-1 (i.e. a percentage) of values that match in the same position. Use this the same as you would use a built-in function. You could then apply normal conditional formatting to this value.

It has an optional True/False (default false) of whether it should be case-sensitive.

"Bob ate Dinner" vs "Bob ate Dinner." would be 0.66, as it does not ignore punctuation.

"Bob Bob Bob Bob" vs "Bob Bob" = 2/4 = 0.5--when strings contain different number of words, the bigger of the two is the denominator, but your numerator maxes out at the smaller of the two.

Function ScorePair(stringInput As String, stringTarget As String, Optional caseSensitive As Boolean = False) As Double

Dim scoreNum As Integer
Dim scoreDen As Integer
Dim splitInput As Variant
Dim splitTarget As Variant
Dim theScore As Double
Dim sizeTarget As Integer
Dim sizeInput As Integer
Dim loopSize As Integer

scoreNum = 0
i = 0

'Extract strings into arrays of words
splitInput = Split(stringInput, " ")
splitTarget = Split(stringTarget, " ")

'Get sizes of arrays to know how much to loop (smaller of two) and what to set denominator at (bigger of two)
sizeInput = UBound(splitInput, 1)
sizeTarget = UBound(splitTarget, 1)
scoreDen = WorksheetFunction.Max(sizeTarget, sizeInput) + 1
loopSize = WorksheetFunction.Min(sizeTarget, sizeInput)

'Loop through arrays comparing them by matching position
For i = i To loopSize
    If caseSensitive Then
        If splitInput(i) = splitTarget(i) Then
            scoreNum = scoreNum + 1
        End If
    ElseIf LCase(splitInput(i)) = LCase(splitTarget(i)) Then
        scoreNum = scoreNum + 1
    End If

'Calculate the score as percentage
theScore = scoreNum / scoreDen
ScorePair = theScore

End Function
share|improve this answer
Forgive the novice question but once I plug that into Visual Basic Editor, what do I do? Thanks – user1470242 May 15 '14 at 16:14
You place the code into a module. In a cell type '=scorepair(a1,a2)' – wbeard52 May 15 '14 at 16:26
This one is doing ok but I don't care so much for the placement of the word. – user1470242 May 15 '14 at 16:42
So, would you like it to work the same as it does, except without having to be the same position? So "Bob Ate A Cow" = "A Cow Ate Bob"? – Madball73 May 15 '14 at 17:07

Your question is also not totally clear. Does the actual name matter (John, Bill)? Will the fruit matter? Or is about 3 conditions

  1. Names match and feelings towards the fruit match
  2. Names don't match but feelings towards the fruit match
  3. Names don't match and feelings towards the fruit don't match

I've assumed that the only thing that matters are the persons name, and if they have the same feeling towards something. Therefore, the persons name can be anything and if both Bill and Claire hate the fruit, I have assumed that is also an orange. You have the code now, so it should be easy to amend!

Sub Button1_Click()

Dim row As Integer
row = 1
Do While (True)

If Range("A" & row).Value = "" Then
    Exit Do
End If

Dim score As Integer
score = 0

Dim wordsA() As String
wordsA = Split(Range("A" & row).Value, " ")

Dim wordsB() As String
wordsB = Split(Range("B" & row).Value, " ")

If wordsA(0) = wordsB(0) Then
    score = score + 1
End If

If wordsA(1) = wordsB(1) Then
    score = score + 1
    score = score + 3
End If

'2 = both same name, both feel same way
'1 = different name, both feel same way
'3 = differet feelings

Select Case score
   Case 1
   Case 4
Range("B" & row).Interior.ColorIndex = 46

   Case 2
   Range("B" & row).Interior.ColorIndex = 43

   Case 3
Range("B" & row).Interior.ColorIndex = 30

End Select

row = row + 1


End Sub

enter image description here

share|improve this answer
Oh so I just assumed conditional formatting would have a way so for me, any solution works. Ok forgive me for being novice, I have plugged the code into Visual Basic Editor but I don't know where to go from there to get it to run.. any guidance would be great. Thanks! – user1470242 May 15 '14 at 15:57
Well, to test it, first create a COPY of the excel file!!! Then, on the copy, just click the Play (green arrow) button to first see if it does what you want (or not) @user1470242 – Dave May 15 '14 at 16:20
Where does the play button show up on Excel for Mac? I am not seeing it. #novicequestion2 – user1470242 May 15 '14 at 16:27
I go to Tools > Macro > Macros and then hit Run but nothing happens. – user1470242 May 15 '14 at 16:28
hmmm... do you have macros enabled? I don't know Mac at all, all I know is Excel-Mac can run macros hence the post. As to HOW you execute it, I think you'd be best doing a Google search, I don't think I can help much with that... Sorry @user1470242 – Dave May 15 '14 at 16:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .