Conditional Formatting with code

I have a worksheet that has a project # on the left in Column A.
A B C D
Project #1
Week 1 Rod (B2)
Week 2 Rod

Project #2
Week 1 Rod (B6)
Week 2 Joe

Then I may have up to 15 resource names, Rod, Joe, Fred, Ethyl....
What I would like to do is use code (VBA or macro) to conditionally format the Cells - Fill cell with Red and the text White in each cell when duplicates occur in the same column to tell me that I have already used Rod in Cell B2 and Cell B6 (Example above).

This needs to be done for every column and again I would have up to 15 names.

So I would like to use VBA or a macro to say:
Look in each column for duplicate names, but only in each colum and turn each cell where there is a duplicate name Red with white text to let me know that I already ahve allocated that resource.

Make sense??

I would also like to create a script that would say if you find the word completed in any cell then make the cell green with white text..

I am new to VBA in Excel so i would need some steps through on what to enter and how to enter it in Excel.

Thank You.
Rod

Rod

Any particular reason you want code for this instead of conditional formatting???

rylo

I may not be understating it correctly, but I found that I was not able to copy Conditional formatting from one column to the next or a series?

Is there a way to set up one column and drag it (for lack of a better word) to as many columns as I want?

Rod

Rod

You should be able to copy and just paste the formats across both rows and columns. You have to make sure your cell reference formulas are not absoluted so it will change the references as you copy it.

Taking your example you could have a conditional formula
B2: =AND(B2<>"",SUMPRODUCT(--($A$1:$A$12=A2),--($B$1:$B$12=B2))>1)
Make your pattern red and your font white. Then if you copy B2, you can paste formats down to B12 and it should work.

If you want to move it across columns, then remove the absolute from the ranges, and copy across.

If you can't get it to work for your situation, put up an example file that covers your setup and requirements, with non sensitive data for review.


rylo