CODEWITHSUNDEEP
excelrowhighlight
pball
pball

Reputation: 47

Excel: Highlight row and column by given numbers

I have a chart I am trying to automate in excel. There are two input values, one is in the first column and the second is in the row containing the first input. For example I would have the inputs 3 and 50. So the row starting with 3 would be highlighted and then the column containing 50 in that row would be highlighted. This would make it easy to see that result b is the correct outcome.

Example Chart .

I have the row highlighting figured out with a simple conditional format. Using something like =$A4=$F$1 where F1 is the input value.

The column highlighting is more difficult as it has to be based off finding a value inside of a row that is determined by another value. This is what I could use some help with.

Upvotes: 0

Views: 155

Answers (2)

PeterH
PeterH

Reputation: 1040

EDITED Select Cell A18

Use formula to determine CF,

And use =OR($A18=$A$33,INDEX(A$18:A$31,MATCH($A$33,$A$18:$A$31,0),0)=$B$33)

Then apply the formula to your range

If your table goes bast 6 rows, change A$6 in the count if part, to the bottom row of the table.

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152535

The conditional formatting formula is:

 =OR(ROW(A1) = MATCH($F$1,$A:$A,0),COLUMN(A1) = MATCH($F$2,INDEX($1:$1048576,MATCH($F$1,$A:$A,0),0),0))

enter image description here

Upvotes: 2

Related Questions