*******I Need Help******

[Rohinikumar]

Hello All,

I hope some one expert can help my problem...that is i have excel book for
two sheets in sheet one some coloumns "NO"with back ground highlited Yellow
colour.that is every 15days we have checked our vehicles if some documets are
not we will mention NO.after 15days again we have to check same vehicles that
day also if it is not the same documets automaticly it will come No with Red
colour background????i want to know how it is possible...if you are not
understand just give me your mail id i will send you the excel sheet.pls help
me for my great future in my office.

 

[Jacob Skaria]

Check out this link on Conditional formatting with dates

http://www.mrexcel.com/articles/due-dates-with-conditional-formatting.php

 

[Rohinikumar]

Hi which you have given that si not correct for my question....can you give
me your mail id i will send excel sheet.

 

[Rohinikumar]

In the excel sheet I just highlighted colors manually but I want auto formula
like in the first sheet some vehicles doesn’t have some documents then we
mentioned No with yellow colour and Yes in green color, later 15 days again
we have to audit the same vehicles then we compare both sheets that time if
same documents have in the same vehicle then it will come in green color if
not the document it will come to in red color. Is it possible?

 

[Jacob Skaria]

Why dont you post few sample data or explain how the data is arranged...

 

[Rohinikumar]

Now you can understand what i am asking

I am using excel book with two sheets In the excel sheet1 monthly twice we
have auditing our company vehicles some vehicles doesn’t have some documents
then we mentioned “No†with back ground yellow color and If “YES†then we
mention in green color in the sheet1, later 15 days again we have to audit
the same vehicles and we compare both sheet1 to sheet2 that time if same
documents have YES in the same vehicle then it will come in green color if
not the document in same vehicle and same document it will come in “NO†red
color back ground. I don’t idea how to compare and how to put formula. Is it
possible?


Sheet 1
Reg No. Insurance Road Tax Vehicle TYESpe Model Speed GoverNOr Seat Belt

TN22 BZ 7101 YES NO BUS 2008 NO NO
TN22 BZ 8211 YES NO BUS 2008 NO NO
KA01 B 8639 YES YES BUS 2006 NO NO
TN22 BX1712 YES YES Mini BUS 2008 NO NO
KA01 B 8056 YES YES BUS 2006 NO NO
TN22 AW 4776 YES NO BUS 2008 NO NO



Sheet 2
Reg No. Insurance Road Tax Vehicle TYESpe Model Speed GoverNOr Seat Belt

TN22 BZ 7101 YES NO BUS 2008 NO NO
TN22 BZ 8211 YES NO BUS 2008 NO Yes
KA01 B 8639 YES YES BUS 2006 YES Yes
TN22 BX1712 YES YES Mini BUS 2008 NO NO
KA01 B 8056 YES YES BUS 2006 NO NO
TN22 AW 4776 YES NO BUS 2008 NO NO

 

[Jacob Skaria]

Hi Rohinikumar

Select sheet1 data and name that range as say "myName" . From menu
Insert>Name>Define

1. In Sheet2 select the data range say B1:G1000. Make sure the active cell
is B1.
Active cell will have a white background even after selection
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=AND(VLOOKUP($A1,myName,COLUMN(),0)="NO",B1="NO")

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.

 

[Rohinikumar]

Hi Jacob thanks for your help but some little confustion,could you give me
your Email ID i will send my excel sheet then you can put format and send it
back to me..pls