Question on cell colour and a search formula


New Member
Nov 9, 2005

I Used this site yesterday for the first time and was impressed by the speedy and accurate response. So a big thank you for that.

Now onto my new issue.

I have a spreadsheet to keep track of deliveries made. For each delivery i have to request proof of delivery. Each delivery has a unique identifier. What i want to be able to do is have column A with the unique identifiers, B C & D filled with other vital info. In column G i want to able to type in the identifying numbers for all the jobs in which i have requested and if a match is found in Column A i'd like to colour it yellow. In column H i'd like the same formula but to colour the column A cell red. Meaning i can quickly see which Proof of deliveries i've requested and which ones i've recieved.

Could anyone help me with this please :confused:

Thank you in advance

Ricky Coales :LOL:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sorry let me try again

I have a spreadsheet with several columns of information. Column A has a Roadline number (unique). In column G i type in Roadline numbers i have requested more infomation on and in column H i type in the roadlines that i recieve more information on.

Ideally what i want is when i type in a roadline number in column G i would like it to colour the matching roadline cell in column A yellow (theres always going to be only one matching roadline number). When i type in the roadline in column H i would like it to find the match again in Column A and this time colour it Red.

Does that help at all ?
Upvote 0

OK, I think I understand.

Select A2:A?? that house the Roadline numbers.

Through the menus, go Format|Conditional Formatting...|Condition 1|Cell Value Is|equal to|=$G$2
Click 'Format' and choose yellow, OK
Click 'Add>>'
Condition 2|Cell Value Is|equal to|=$H$2
Click 'Format' and choose red, OK, OK

You should end up with something like this. Is that what you wanted?
Mr Excel.xls
1Roadline NoRequestReceive
Check Deliveries
Upvote 0
OK i thought that was just the thing and its close but still going to be long winded. So if anyone could help more i'd really appreciate it.

Hopefully they'll be a table just underneath this :-

Right to start from the beginning. First thing in the morning I have a stack of paperwork on my desk. From this I update column A,B,C and D. I then use all the unique identifiers in column A to request extra information. (Which I get back possibly days later). I need to keep track of the items I've requested and recieved and those I've not done anything with.

So when I request them I look down the list until i find the number (not a quick task with over 2000 entires) Once I've found it I colour it yellow to show I've requested the information, and when I recieve it I repeat the step and colour it red. (to show the staus of each item)

Ideally what i'd like to be able to do is just add the unique identifier to a list of numbers in column G when requested and type it in again in column H when recieved and have excel somehow compare all of column A to all of Column G and if it finds a match colour it the appropriate colour.

The earlier solution would have worked but i'd have to copy and edit the formula for each and every entry since conditional formatting doesn't allow you to select a range of cells.

Can anyone make any suggestions at all, i have no qualms with adding extra columns should it be easier but i've no idea where to begin.

Thank you so much for any help you can give me
Upvote 0
While you have column A selected, In the conditional Format dialogue, select Formula Is and enter

=MATCH(A1,G:G,0) and then choose your format

add a condition

=MATCH(A1,H:H,0) if you want a different colour then.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back