Conditional Formatting question running XL2010

[mcmahobt]

Hello everyone,

Quick question to see if this is possible without the use of VBA - I have created a conditional formatting rule that assigns different colors to a product based on version number. For example, if a product has a version number of 3.0, the text will be formatted as the color black, according to this equation:

=AND(EXACT(C$3,'Fielding Plan '!$D2),IF(ISBLANK('Fielding Plan '!$E2),FALSE(),VALUE(LEFT('Fielding Plan '!$E2,1))>0),IF(ISBLANK('Fielding Plan '!$E2),FALSE(),VALUE(LEFT('Fielding Plan '!$E2,1))<5.4))


My product versions range all the way up to version 10, and as you can hopefully tell, the values within which the product version falls dictates the color. This has worked fine up until our products have started to undergo small iterations in which the version becomes more detailed, say 3.0.1

Therefore, my question is this: Is there a way to distinguish, using an if statement and some other functions, a difference between say - a product with a version number 3.0.1 and 3.0.2?

Any help is appreciated!

<tbody>
</tbody>

 

[Latchmaker]

Perhaps it is time to revist the reason for color differentiation as the version# is a clear indicator of a change why not combine a concatenated version of ProductID and Version and base your color on this instead. =concatenate(CellRef of "productID",CellRef of "Version")="productIDVersion" you can add any other distictions to this concatenate formula to signify the exclusive data set ......then set format to color of choice

 

[mcmahobt]

Latch,

I have considered that. However, because what I am working on is meant to be high level, and act as more of a visual guide to show the evolution of the products as opposed to the exact status of them, this is not possible. I appreciate your advice though!