Question on cell colour and a search formula

RICK150

New Member
Joined
Nov 9, 2005
Messages
44
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 :confused:

Thank you in advance

Ricky Coales :LOL:
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,580
Office Version
  1. 365
Platform
  1. Windows
In column H i'd like the same formula
Same formula as what?

I can't quite get what you are after.
 

RICK150

New Member
Joined
Nov 9, 2005
Messages
44
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,580
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGH
1Roadline NoRequestReceive
2A123B456A534
3B456
4C789
5A534
6H892
Check Deliveries
 

RICK150

New Member
Joined
Nov 9, 2005
Messages
44
Yes thats exactly what i wanted thank you so much. and sorry for being so vaugue.
 

RICK150

New Member
Joined
Nov 9, 2005
Messages
44
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
ABCDEFGH
1Roadline123RequestedReceived
2123blahblahblah
3124blahblahblah
4125blahblahblah
5126blahblahblah
6127blahblahblah
7128blahblahblah
8129blahblahblah
9130blahblahblah
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

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 MrExcel.com.
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 "mrexcel.com".
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
Top