CODEWITHSUNDEEP
excelformatrowhighlight
franciscofcosta
franciscofcosta

Reputation: 843

Highlight table row depending on another cell's value

I have a table with a set of rows and information on several countries. The countries are in the first column of the table. I would like to highlight a table row depending on the country selected in another sheet.

The table is in Sheet "Rankings" and the country is selected in Sheet "Report", on cell B4.

I have tried conditional formatting but it only highlights the first cell on the table (not the entire row) and it only does that if the country chosen is the one that appears first on the table.

I guess what I would need is a formula that would run through the first column of the table, detect the row in which the country selected is and then highlight that entire table row.

How would I be able to do this?

Upvotes: 0

Views: 518

Answers (1)

DMM
DMM

Reputation: 1112

The answer has been strongly hinted at in the comments above. However, I'll spell it out

  1. On sheet Rankings select all the rows where the country names might appear in the first column
  2. Open the New Formatting Rule dialog by selecting Conditional Formatting/New Rule from the Home tab of the ribbon
  3. In "Select a Rule Type" (top part of dialog) select "Use a formula to determine which cells to format" - the bottom part of the dialog will change
  4. In the box labelled "Format values where this formula is true:" enter =$A1=Report!$B$4
  5. Click the "Format" button, choose a suitable format and return to the New Formatting Rule" dialog and click OK

Step 4., assumes that the first row selected in step 1. is Row 1. If this is not the case, the formula in step 4. should be changed to reflect the correct first row. For example if you have headers in Row 1 of sheet Rankings and the first row that contains a country is Row 2 then change the formula to =$A2=Report!$B$4.

If you use the "point and click" method of building the formula in step 4., Excel uses absolute addresses and it will create a subtly different formula which will not give you the results you desire.

Upvotes: 1

Related Questions