Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas in conditional formatting
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas in conditional formatting
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 FormatConditional 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 -- Biff Microsoft Excel MVP "Alaa Masry" <Alaa wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas in conditional formatting
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 InsertModule 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 --- "Alaa Masry" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting using formulas | Excel Discussion (Misc queries) | |||
Conditional Formatting-No Formulas | Excel Worksheet Functions | |||
Conditional formatting formulas | Excel Discussion (Misc queries) | |||
Conditional Formatting and If/Then Formulas | Excel Discussion (Misc queries) | |||
Conditional Formatting - Formulas | Excel Discussion (Misc queries) |