1

I want to Highlight entire row when TWO similar name with specific similar number. (Excel Screen shot attached ) Example -> Excel file have A to J column, A10 and A11 both are with same name ASIANPAINT and J10 and J11 are similar with specific number 3 so want to highlight entire row with GREEN

Example -> same file A16 and A17 both are with similar name BEML and J16 and J17 are similar with specific number 2 so want to highlight entire row with YELLOW

Means:

  • TWO Similar name with number 3 = GREEN HIGHLIGHT entire row
  • TWO Similar name with number 2 = YELLOW HIGHLIGHT entire row
  • Do you mean you only want to highlight when there are 2 together on adjacent rows? – Mark Fitzgerald Jul 16 '18 at 12:30
  • Yes 2 names together on adjacent rows and with J column 2 numbers together with adjacent (J column have 2 and 3 value so when 2 number appear two times against any two names together, needs to mark YELLOW highlight, and same for 3 value need to mark GREEN highlight. Thank You. – JUSTWIN Jul 16 '18 at 13:26
  • expected result shown in my attached screen shot . – JUSTWIN Jul 16 '18 at 13:28
0

The following can only be applied from row 2 down so if you don't have a header row, inset a blank row above your data.

You need conditional formats to check:

  1. Is column J = 2 or 3 in two separate CFs
  2. In both those CFs, also check if the number in column J is the same as the one in the row above it OR below it. They will only be the same if they are in adjacent rows but the number itself doesn't matter.
  3. In both those CFs, also check if the name in column A is the same as the one in the row above it OR below it. They will only be the same if they are in adjacent rows but the name itself doesn't matter.

The CF formula for green highlight will be:

    AND($J2=3,OR($J2=$J1,$J2=$J3),OR($A2=$A1,$A2=$A3))

Apply this to range A2:J13 or whatever your last row is.

For the yellow highlight, replace $J2=3 with $J2=2.

  • Hi Mark Fitzgeralad. Thanks a TON for your guidance, Yes its work most near to my requirement, when selected "2" its highlighting all "2" with selected Yellow colour that's very good. But my Final requirement is highlight TWO similar name when J column both have "2" with this formula all name highlighted where "2" have in J column, So example there is 2 similar name BEML and against this name J column one row have "1" and another have "2" with this formula "2" value highlighted with yellow colour which I dont want. Thank you for your given time. if possible please help me. – JUSTWIN Jul 17 '18 at 7:44
  • Hi Mark Fitzgeralad. Thanks a TON for your guidance, Yes its work most near to my requirement, when selected "2" its highlighting all "2" with selected Yellow colour that's very good. But my Final requirement is highlight TWO similar name when J column both have "2" with this formula all name highlighted where "2" have in J column, – JUSTWIN Jul 17 '18 at 7:54
  • So example there is 2 similar name BEML and against this name J column one row have "1" and another have "2" With this formula "2" value highlighted with yellow colour and "1" value not highlighted that is fine BUT I don't want that I want when both have "2" value only then Highlight with Yellow. Thank you for your given time. if possible please help me. – JUSTWIN Jul 17 '18 at 7:54
  • @JUSTWIN please see my edit where I've added an additional test to ensure numbers as well as names match those above or below it. – Mark Fitzgerald Jul 17 '18 at 9:01
  • Wowwww Superb. Mark you are Genius - this EXACTLY working as per my requirement. Thanks a LOT..... Thank you so much..... – JUSTWIN Jul 17 '18 at 9:38
0

enter image description here

How it works:

  1. Select entire data range.
  2. On Home Tab click Conditional Formatting.
  3. Select New Rule and apply following formula one by one.

N.B. This actions needs Two Rules to format.

Formula 1: =AND($A10="Asian Paint", $J10=3)

Formula 2: =AND($A10="Beml",$J10=2)

  1. Finally apply an appropriate color format and finsih with Ok.

Note,

1.This will apply color format to all rows which has Asian Paints & 3 also Beml & 2.

2.Adjust cell references in formula as needed.

  • Rajesh, Thanks for your prompt help and really sorry if my questions is wrong, please guide me is there any way to Highlight two similar name without specific name - (here I have given "AsianPaint" and BEML for example but my main point is to highlight whenever two name and numbers are similar, 3=green and 2=Yellow – JUSTWIN Jul 16 '18 at 6:49
  • @JUSTWIN, you mean to say U want to highlight rows witch have other than Asian Paints & Beml with 3 & 2 ? – Rajesh S Jul 16 '18 at 6:53
  • Yes Correct if any two similar name when have 3 or 2 similar numbers needs to Highlight entire row with Green and Yellow – JUSTWIN Jul 16 '18 at 7:13
  • in my excel file total 90 names have same name (180 rows) – JUSTWIN Jul 16 '18 at 7:16
  • @JUSTWIN, My solution is exactly as your recent edited version of Question ,,TWO Similar name with number 3 = GREEN HIGHLIGHT entire row, data range has 2 similar name ASIAN PAINT with 3 are in GREEN !! – Rajesh S Jul 16 '18 at 7:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.