Highlight cells based on 10-15 values and post alternatives

Question

I have a 1600-2000 rows of data in a spreadsheet that changes daily. I would like to highlight any row that contains such as 000000000000053851 in the R column. I had tried conditional formatting using this formula:

=OR($R1="000000000000549543",$R1="000000000000267294,$R1="000000000000053851")

but it seems to highlight a few rows that are blank in column R also.

Using the same numbers highlighted in column R, I would like to have an alternate number and possibly other data pasted into the corresponding row in column S. Each number has a unique alternate number and data to go along with it. This is how I picture a line may look with the highlighting and alternate data next to it:

I intend to record a macro using a few of the different steps that are likely required to do this. Any way, whether conditional formatting or an array formula, would be great to help with this.

Answer 1

For the sample provided, a CF rule of:

=LEFT(R1,12)="000000000000"  

applied to ColumnR will format the sample and if you wish the cell immediately to the right. It will not highlight blank cells.

HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true:, enter formula from above and Format... with Fill red, OK, OK.

If you want the cell to the right to be formatted also, select ColumnsR:S instead of just ColumnR and change R1 in the formula to $R1.

For the cell to the right you provide few details but VLOOKUP in conjunction with a two-column table should suit. Column on the left being the ColumnR value, with the right-hand column for the same row the ColumnS value required. This table could be placed almost anywhere.

This could all be done with Record Macro.