You are here:

Excel/conditional formating with fill

Advertisement


Question
Dear Sir,
Cell A1:A12 I have formula =randbetween(1,21)
cell C1=1,cell C2=2,cell C3=3,cell D1=4,cell D2=5,cell D3=6....cell I1=19, cell I2=20, cell I3=21. This is my layout. My question is i want to conditional format and fill with yellow only those cells within range C1:I3 whose value matches with A1:A12. Please show me what formula i put in conditional format to achive this.

Thanks.

Answer
Sahil:

1) Select cells C1:I3 (make sure C1 is the first cell you click when selecting the range C1:I3)
2) Choose conditional formatting and select the option "Use formula to determine which cells to format".
3) In the box where it asks what formula to use enter the following:

=MATCH(C1,$A$1:$A$12,FALSE)

In the above formula it is impotant for C1 to not be fixed/static (ie, not have $'s around the reference).  You will want the $'s around the$A$1:$A$12.

4) Click OK.

This should have the desired effect.  If you reivew the conditioanl formatting on your workbook it should show the above formula applies to =$C$1:$I$3.

I have a sample spreadsheet if you need it.  Just post your email address in your "thank/rate the expert" box.

Also remember to donate if this is a valid solution to your question.  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Nathan

Expertise


This expert requires a donation of $5 for questions answered (satisfaction guaranteed). Isn't $5 a value for this experts time?
Microsoft Excel questions related to advanced formulas, Pivot Tables, filters, forms, graphs, and just about anything else (EXCEPT Visual Basic Coding/Programming and Macros, I don't answer questions in those categories).

Experience

I have been using spreadsheets since Lotus 1-2-3 was released.

Education/Credentials
Certified Public Accountant (CPA)

©2016 About.com. All rights reserved.