Question on cell colour and a search formula

[RICK150]

Hello.

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

Thank you in advance

Ricky Coales

 

[Peter_SSs]

In column H i'd like the same formula

Same formula as what?

I can't quite get what you are after.

 

[SteveO59L]

Have a look at conditional formatting

 

[RICK150]

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 ?

 

[Peter_SSs]

RICK150

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
	A	B	C	D	E	F	G	H
1	Roadline No						Request	Receive
2	A123						B456	A534
3	B456
4	C789
5	A534
6	H892
Check Deliveries

 

[RICK150]

Yes thats exactly what i wanted thank you so much. and sorry for being so vaugue.

 

[RICK150]

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 :-

Book1.xls
	A	B	C	D	E	F	G	H
1	Roadline	1	2	3			Requested	Received
2	123	blah	blah	blah
3	124	blah	blah	blah
4	125	blah	blah	blah
5	126	blah	blah	blah
6	127	blah	blah	blah
7	128	blah	blah	blah
8	129	blah	blah	blah
9	130	blah	blah	blah
Sheet1

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

 

[NBVC]

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.