0

I am working in Excel Professional Plus 2010. I have searched and the only things I've found are either doing individual line by line highlighting or searching and highlighting an entire column. I have three long columns I am comparing (E & O & P), and I want it to compare and highlight line by the corresponding line. For example, if E38=7, then I want O38 & P38 to be highlighted. I cannot add columns to put in a formula for comparison as it is a standard form. I need it to search for all instances of "7" in Column E and then highlight the corresponding cells in those rows in columns O & P.

image

Kate
  • 1
  • 1

2 Answers2

1

Use conditional formatting, set a rule by formula. Something like this: Example Just make the columns/values as you need them.

click here
  • 814
  • 2
  • 10
  • 24
  • Thank you! I just tried your suggestion and it doesn't seem to read it correctly. It highlighted, but not on the correct line. I want it to search column E for all instances of 7, and then highlight the corresponding rows where 7 is true in columns O & P. – Kate Mar 17 '16 at 18:01
  • You changed the formula to your requirement, `=$E1=7`, and changed "applied to" to `=$O:$P`, correct? If so, any row with a 7 in the E column will have the O and P cells in that same row formatted however you've set the formatting. – tittaenälg Mar 17 '16 at 20:01
  • Yup, that's exactly what I did. It ended up highlighting random rows in the O & P columns and only a couple of them had 7's in the E column. It even highlighted some where the E column was null. :( – Kate Mar 17 '16 at 20:16
  • @kate I built a test workbook that with merged cells like yours appears to have and think I saw what you described. Instead of highlighting O and P on the line with the E=7, O and P were being highlighted 2 rows above the line where E=7. Is your workbook always highlighting a certain number of rows above an E=7 row? I managed to get my workbook highlighting the correct row via the following: go to manage rules, and without deleting the malfunctioning rule, go into it in and reentered the formula and target cells. After doing this the formatting magically started working as it was supposed to. – tittaenälg Mar 18 '16 at 03:41
  • @Kate, what tittanalg mentioned is something that I come across too sometimes. It'll change `$A1` to something like `$A64023` Not sure why excel will change the reference from what is typed in the first time sometimes. – click here Mar 18 '16 at 11:37
  • Yes! That's exactly what happened! It had changed the formula to that^, so I reentered it and now it magically works, haha. Thank you for waving your elder wand and fixing it. :) – Kate Mar 18 '16 at 13:51
0

The Column you want to highlight is where the formatting is applied to:

=$O:$P

Set the rule type to "Use a formula to determine which cells to format" and enter a formula that references the column (with $ to keep it static) and the first row in the array (without $ to allow it to dynamically apply the formatting to each row):

=$E1="a"

Condition

bydesignproductions
  • 1
  • 3