Hi Dasha,

Are the cells you are wanting to be conditionally formatted the same
cells with the data validation dropdown?

I thought that B15:B150 all had data validation using a list named
CODES_Abriev's on a sheet named CODES & Abrieviatons. I figured that
when one of your users used the data validation dropdown in any of the
cells in B15:B150 on the sheet named TENDER TEMPLATE x1 that the list
of choices consisted of items with first three characters being either
"Pip" (from "Pipes etc"), "Pit" (from "Pit's") or "Pie" (from
"Pieces").

If this is correct, then what I thought you could do was select
B15:B150 (the cells with the data validation), then go Format|
Conditional formatting.

The Conditional Formatting dialog that appears has a textbox on the
left with a dropdown. You should choose "Formula Is" for this textbox.

In the textbox immediately to its right you should type the formula...

=LEFT($B15,3)="Pip"

then click the Format button and adjust the green formatting.

With the above formula in place in the conditional formatting, say the
user clicks on B30 then selects Pipes-Water/{1}EW) from the data
validation dropdown. Excel then calculates the formula...

=LEFT($B30,3)="Pip"

Notice that the row number in the formula is 30, not 15. This is
because the row number 15 in the original formula did not have a $
sign to its left. This is how you control which cells Excel uses when
is tests for conditional formatting.

Since B30 contains "Pipes-Water/{1}EW)" , the formula LEFT($B30,3)
returns the first 3 characters of  "Pipes-Water/{1}EW)", which is
"Pip".
So, the result of calculating =LEFT($B30,3)="Pip" is TRUE.
When a conditional format calculation is TRUE the chosen format, in
this case green text or background, is applied.

Similarly for the other two format conditions...

The formula for the blue formatting is =LEFT($B15,3)="Pit", and for
the orange formatting =LEFT($B15,3)="Pie"

If my interpretation of what you have and what you are trying to
achieve is not correct, then let me know, and we can have another go.

Ken Johnson