more than 3 conditional formats

[spudgun44]

Hi, what I want is to change the color of a range of cells based on 10 different criteria, the problem I have is that I also need to copy and past these (and on occation remove the cells) as the sheet is updated.

I know that this could probably be done with VBA, but will it copy

say for example, if cell a1:a10 have the coding so they change red if A1 = 1 and then I copied A1:a10 and pasted them in to B1:B10 will B1 change red if B1=1? this is what I wanted it to do.

Is there a way that I can determine the cell colour within an 'IF' statement?

 

[Mexcel]

It is indeed possible to write a VBA function to get the cell color. You would need to google to find it.

Office 2007 would be your best option since that can handle much more conditional formatting rules.

 

[gsheppar]

I have seen a few posts in the past regarding this question on the forum. Unfortunately I have not had the time to attempt doing it using VBA. I'm sure that you can copy the data with the conditional formatting. I just did a quick search on the forum "Conditional Format VBA" and here are a couple of links that I saw that might help. Sorry I can't help much more though, I would love to sit down and try and write some code for it.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=90
http://www.mrexcel.com/forum/showthread.php?t=413510&highlight=VBA+Conditional+Format
http://www.j-walk.com/ss/excel/usertips/tip045.htm
http://www.mrexcel.com/forum/showthread.php?t=407280&highlight=VBA+Conditional+Format
http://www.mrexcel.com/forum/showthread.php?t=404344&highlight=VBA+Conditional+Format

I'm not an expert, but I don't think you can determine a cell color within the IF statement, but you could do a work around using an if statement and then reference that cell when you create your conditional formatting to format based on the result of the IF statement.