formulas in conditional formatting

[Alaa Masry]

i need to change the format of a cell when someone replaces the formula
(SUMIF formula)of a cell and enter the number manually, and apply this format
to a column of cells.

For example the prices in the cells are calculated using the (SUMIF) formula
to fetch the price from the price list sheet compared with the part number,
sometimes we need to modify the price for a certain part number (special
price, …etc.) so we will enter this price manually, and we need to change the
format of this cell to show these price modifications.

 

[T. Valko]

In other words, if the price doesn't match the price for the item from the
price list highlight the cell?

One way....

Sheet2 A1:B10 = price list
Give this range a defined name like, say, pricelist

Apply the cf....

Sheet1 A1:A10 = part number
Sheet1 B1:B10 = range of SUMIF formulas

Select the range B1:B10
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:

=B1<>VLOOKUP(A1,pricelist,2,0)

Click the Format button
Select the style(s) desired
OK out

 

[Max]

This generic option should work if you want to CF cells which do not contain
any formula, and are not blank

Install the IsFormula UDF below (taken from a post by Bob Phillips),
then apply CF using "Formula Is" which uses the UDF

To install the UDF:
Press Alt+F11 to go to VBE
Click Insert>Module
Copy n paste the UDF into the code window (whitespace on the right)
Press Alt+Q to get back to Excel

In Excel,
Select the range to be conditionally formatted
For example, select col B (with B1 active),
Apply the CF using "Formula Is":
=AND(NOT(IsFormula(B1)),B1<>"")
Format to taste > Ok out

'----
Function IsFormula(rng As Range) As Boolean
If rng.Count > 1 Then
IsFormula = CVErr(xlErrValue)
Else
IsFormula = rng.HasFormula
End If
End Function
'---

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik