Formula required to select rows with columns having a particular number

Asked By 0 points N/A Posted on -
qa-featured

How to select multiple rows in one instant? I am looking for a particular value such as R102 or C22 etc in th excel cells. If i find these values in any one of th cells i need to highlight the entire row in any color. I know about conditional formatting but how to find a formula which meets my requirement. The values can be present in multiple rows and if yes, all the rows have to be highlighted with same color. Similar operation for different keyword and different color. Can you all please help?

SHARE
Answered By 564935 points N/A #194317

Formula required to select rows with columns having a particular number

qa-featured

This is quite a formatting you want to do with your Microsoft Office Excel worksheet. I was able to find a formula that applies to the task you want to accomplish in your Microsoft Office Excel worksheet. This method uses conditional formatting and it checks every cell in your entire worksheet for a specific value and it highlights the entire row where the value appears.

Here’s how to do it. Start Microsoft Office Excel. Make sure A1 is the selected active cell. In Home tab under Styles section, click Conditional Formatting and then select New Rule. See image.

In New Formatting Rule dialog box under “Select a Rule Type” section, select the last option: “Use a formula to determine which cells to format”. Under “Format values where this formula is true”, enter the following:

  • =countif(1:1,”R102”)

Replace R102 with the value you want to search. After that, click Format. Select Fill tab then click on the color you want for the highlight. Click OK all through out. See image.

Again, click Conditional Formatting under Styles section and then select Manage Rules. See image.

Select the formula you just entered then under “Applies to”, replace the current value with the one below then click Apply and then OK:

  • =$1:$1048575

In case the formula or the conditional formatting doesn’t work, delete the formula and then create a new one. Make sure when entering “=countif(1:1,”R102”)” and “=$1:$1048575” you do it manually.

Login/Register to Answer

Related Questions