Conditional Formatting Problem #7

Is there a way to make conditonal formatting work when there is a
formula in the cell?

Conditional foramtting works if there is no formula in the cell.  If
one sheet is linked to another that doesn't work either.

Is there a way to get around this? 

Under conditonal formatting there is a conditon "formula is."  What
would you put in there to have the cell format they you would like?
0
Peterzx7 (7)
11/25/2007 8:47:22 AM
excel 39879 articles. 2 followers. Follow

10 Replies
614 Views

Similar Articles

[PageSpeed] 33

There are two different things:

a) the cell, containing a cell formula, a value or nothing

b) the condition, containing a boolean formula (formula is)

- b) can refer to a), but a) referring to a) makes it circular.
- for b) it does not matter, whether a) is a formula or a value

Example:

A1: 50            CondFrmt =A1>40 here works the same as in
A1: =DATE(NOW())

-- 
Moin+Gruss Alexander - MVP f�r MS Excel - www.xxcl.de - mso2000sp3 --7-2


0
ooops (23)
11/25/2007 11:03:59 AM
Hi Peter,

Maybe this.
Sometimes you need to set the Formula is: part of
conditional formatting to the same formula as you
have in your cell, rather than working off the value
that it returns.

HTH
Martin


"Peter" <Peterzx7@Peterzx7.com> wrote in message 
news:69dik39slsbih03i925131sv7jhonpvect@4ax.com...
> Is there a way to make conditonal formatting work when there is a
> formula in the cell?
>
> Conditional foramtting works if there is no formula in the cell.  If
> one sheet is linked to another that doesn't work either.
>
> Is there a way to get around this?
>
> Under conditonal formatting there is a conditon "formula is."  What
> would you put in there to have the cell format they you would like? 


0
mtmw (348)
11/25/2007 2:25:16 PM
CF (Conditional Formatting) can work on a cell that contains a formula of 
it's own, completely separate from the CF, *and* it can work spanning other 
sheets.

Post back with some specific questions if you need assistance creating CF 
formulas.
-- 
Regards,

RD
----------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------------

"Peter" <Peterzx7@Peterzx7.com> wrote in message 
news:69dik39slsbih03i925131sv7jhonpvect@4ax.com...
> Is there a way to make conditonal formatting work when there is a
> formula in the cell?
>
> Conditional foramtting works if there is no formula in the cell.  If
> one sheet is linked to another that doesn't work either.
>
> Is there a way to get around this?
>
> Under conditonal formatting there is a conditon "formula is."  What
> would you put in there to have the cell format they you would like? 


0
ragdyer1 (4060)
11/25/2007 3:57:35 PM
Here is a formula I have in a cell.  I drag it down over many cells,
so it changes slightly as you drag it down.  If I try to paste it into
the conditional formatting box it won't let me do that.  

=IF(K17="","",IF(F17=J17,0.5*(H17*K17),IF(G17=J17,0.5*(I17*K17)))-(H17+I17))

So, I'm not really sure how to proceed.


On Sun, 25 Nov 2007 10:57:35 -0500, "RAGdyer" <RAGdyer@CUTOUTmsn.com>
wrote:

>CF (Conditional Formatting) can work on a cell that contains a formula of 
>it's own, completely separate from the CF, *and* it can work spanning other 
>sheets.
>
>Post back with some specific questions if you need assistance creating CF 
>formulas.

0
Peterzx7 (7)
11/26/2007 2:28:44 AM
Hi Peter,

You can use the FormatPainter button to copy Conditional Formatting.

Select the cell(s) you want to copy from. Click the FormatPainter
button. Selec the cells you want to apply the formats to.

Best
Mark

0
11/26/2007 9:55:54 AM
Hi Peter,

Your formula returns one of the following,

""
0.5*(H17*K17)     - no need for the brackets here
0.5*(I17*K17)-(H17+I17)

so the formulae to use in CF
are either
=""
=0.5*H17*K17
=0.5*(I17*K17)-(H17+I17)

or maybe all three as Condition 1, Condition 2, Condition 3
As mentioned by Mark you can use the Format Painter to drag them down
or you can select all of your cells first before you apply the CF and
they will automatically adjust down the sheet.

HTH
Martin


"Peter" <Peterzx7@Peterzx7.com> wrote in message 
news:rlbkk3t946g0617l3jlh3nkvs6hor8thu3@4ax.com...
> Here is a formula I have in a cell.  I drag it down over many cells,
> so it changes slightly as you drag it down.  If I try to paste it into
> the conditional formatting box it won't let me do that.
>
> =IF(K17="","",IF(F17=J17,0.5*(H17*K17),IF(G17=J17,0.5*(I17*K17)))-(H17+I17))
>
> So, I'm not really sure how to proceed.
>
>
> On Sun, 25 Nov 2007 10:57:35 -0500, "RAGdyer" <RAGdyer@CUTOUTmsn.com>
> wrote:
>
>>CF (Conditional Formatting) can work on a cell that contains a formula of
>>it's own, completely separate from the CF, *and* it can work spanning 
>>other
>>sheets.
>>
>>Post back with some specific questions if you need assistance creating CF
>>formulas.
> 


0
mtmw (348)
11/26/2007 10:39:21 AM
Woops! You didn't actually say which cell is the starting cell for
your formula so say were placing it in M17 then your formulae
in CF should be

=M17=""
=M17=0.5*H17*K17
=M17=0.5*(I17*K17)-(H17+I17)


HTH
Martin



"MartinW" <mtmw@hotmail.invalid> wrote in message 
news:e%23JBqiBMIHA.4476@TK2MSFTNGP06.phx.gbl...
> Hi Peter,
>
> Your formula returns one of the following,
>
> ""
> 0.5*(H17*K17)     - no need for the brackets here
> 0.5*(I17*K17)-(H17+I17)
>
> so the formulae to use in CF
> are either
> =""
> =0.5*H17*K17
> =0.5*(I17*K17)-(H17+I17)
>
> or maybe all three as Condition 1, Condition 2, Condition 3
> As mentioned by Mark you can use the Format Painter to drag them down
> or you can select all of your cells first before you apply the CF and
> they will automatically adjust down the sheet.
>
> HTH
> Martin
>
>
> "Peter" <Peterzx7@Peterzx7.com> wrote in message 
> news:rlbkk3t946g0617l3jlh3nkvs6hor8thu3@4ax.com...
>> Here is a formula I have in a cell.  I drag it down over many cells,
>> so it changes slightly as you drag it down.  If I try to paste it into
>> the conditional formatting box it won't let me do that.
>>
>> =IF(K17="","",IF(F17=J17,0.5*(H17*K17),IF(G17=J17,0.5*(I17*K17)))-(H17+I17))
>>
>> So, I'm not really sure how to proceed.
>>
>>
>> On Sun, 25 Nov 2007 10:57:35 -0500, "RAGdyer" <RAGdyer@CUTOUTmsn.com>
>> wrote:
>>
>>>CF (Conditional Formatting) can work on a cell that contains a formula of
>>>it's own, completely separate from the CF, *and* it can work spanning 
>>>other
>>>sheets.
>>>
>>>Post back with some specific questions if you need assistance creating CF
>>>formulas.
>>
>
> 


0
mtmw (348)
11/26/2007 10:48:14 AM
And just as an aside, there is another set of un-needed brackets

=M17=0.5*(I17*K17)-(H17+I17)
is the same as
=M17=0.5*I17*K17-(H17+I17)

Of course, none of these extra brackets do any harm and can be left in 
place.
Actually I sometimes find it better to use extra un-needed brackets as it 
can
make your formula easier for others to follow.

HTH
Martin



"MartinW" <mtmw@hotmail.invalid> wrote in message 
news:%23WNZonBMIHA.4684@TK2MSFTNGP06.phx.gbl...
> Woops! You didn't actually say which cell is the starting cell for
> your formula so say were placing it in M17 then your formulae
> in CF should be
>
> =M17=""
> =M17=0.5*H17*K17
> =M17=0.5*(I17*K17)-(H17+I17)
>
>
> HTH
> Martin
>
>
>
> "MartinW" <mtmw@hotmail.invalid> wrote in message 
> news:e%23JBqiBMIHA.4476@TK2MSFTNGP06.phx.gbl...
>> Hi Peter,
>>
>> Your formula returns one of the following,
>>
>> ""
>> 0.5*(H17*K17)     - no need for the brackets here
>> 0.5*(I17*K17)-(H17+I17)
>>
>> so the formulae to use in CF
>> are either
>> =""
>> =0.5*H17*K17
>> =0.5*(I17*K17)-(H17+I17)
>>
>> or maybe all three as Condition 1, Condition 2, Condition 3
>> As mentioned by Mark you can use the Format Painter to drag them down
>> or you can select all of your cells first before you apply the CF and
>> they will automatically adjust down the sheet.
>>
>> HTH
>> Martin
>>
>>
>> "Peter" <Peterzx7@Peterzx7.com> wrote in message 
>> news:rlbkk3t946g0617l3jlh3nkvs6hor8thu3@4ax.com...
>>> Here is a formula I have in a cell.  I drag it down over many cells,
>>> so it changes slightly as you drag it down.  If I try to paste it into
>>> the conditional formatting box it won't let me do that.
>>>
>>> =IF(K17="","",IF(F17=J17,0.5*(H17*K17),IF(G17=J17,0.5*(I17*K17)))-(H17+I17))
>>>
>>> So, I'm not really sure how to proceed.
>>>
>>>
>>> On Sun, 25 Nov 2007 10:57:35 -0500, "RAGdyer" <RAGdyer@CUTOUTmsn.com>
>>> wrote:
>>>
>>>>CF (Conditional Formatting) can work on a cell that contains a formula 
>>>>of
>>>>it's own, completely separate from the CF, *and* it can work spanning 
>>>>other
>>>>sheets.
>>>>
>>>>Post back with some specific questions if you need assistance creating 
>>>>CF
>>>>formulas.
>>>
>>
>>
>
> 


0
mtmw (348)
11/26/2007 12:00:31 PM
What does the formula you posted have to do with CF ? ? ?

Your formula will return values!
CF formulas should return logicals (True, False).

When the return is "True", the chosen format is invoked.

Either you don't understand CF, OR ... I don't understand you!

Perhaps you're using the term "CF" by mistake, and you actually mean 
something completely different.
-- 
Regards,

RD
----------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------------

"Peter" <Peterzx7@Peterzx7.com> wrote in message 
news:rlbkk3t946g0617l3jlh3nkvs6hor8thu3@4ax.com...
> Here is a formula I have in a cell.  I drag it down over many cells,
> so it changes slightly as you drag it down.  If I try to paste it into
> the conditional formatting box it won't let me do that.
>
> =IF(K17="","",IF(F17=J17,0.5*(H17*K17),IF(G17=J17,0.5*(I17*K17)))-(H17+I17))
>
> So, I'm not really sure how to proceed.
>
>
> On Sun, 25 Nov 2007 10:57:35 -0500, "RAGdyer" <RAGdyer@CUTOUTmsn.com>
> wrote:
>
>>CF (Conditional Formatting) can work on a cell that contains a formula of
>>it's own, completely separate from the CF, *and* it can work spanning 
>>other
>>sheets.
>>
>>Post back with some specific questions if you need assistance creating CF
>>formulas.
> 


0
ragdyer1 (4060)
11/26/2007 2:30:51 PM
You know what?  All of you guys helped me.  I knew it would be hard to
explain what I was doing, but by piecing together all of the
responses, I got my spreadsheet to do exactly what I want.  It took
some experimenting, but CF was the answer.

Thanks to all,

On Mon, 26 Nov 2007 09:30:51 -0500, "RAGdyer" <RAGdyer@CUTOUTmsn.com>
wrote:

>What does the formula you posted have to do with CF ? ? ?
>
>Your formula will return values!
>CF formulas should return logicals (True, False).
>
>When the return is "True", the chosen format is invoked.
>
>Either you don't understand CF, OR ... I don't understand you!
>
>Perhaps you're using the term "CF" by mistake, and you actually mean 
>something completely different.

0
Peterzx7 (7)
11/27/2007 3:23:23 AM
Reply:

Similar Artilces:

Multiple Condition Formatting
Hi, Can anyone help me on this: =IF(AND(F65<>0,G59="SELECT CUSTOMER"),APPLY FORMATTING, DON'T APPLY FORMATTING) I have been exploring the Conditional Formatting in 2007 and I may be wrong but I don't see a way to set a condition whereby two(2) logical conditions must be met for it to apply the formatting. Any ideas?? http://excel.tips.net/Pages/T002980_Conditional_Formats_that_Distinguish_Blanks_and_Zeroes.html This could easily be applied to other conditions. Mike "Gerard Sanchez" wrote: > Hi, > > Can anyone help me on this: > ...

Outlook 2007 Delay Delivery Problem
I'm having a problem with the Delay Delivery option in Outlook 2007. All new messages default to a Delay Delivery of <Today> at 5:00 PM. How do I turn this feature off? As far as I know, I never turned it on in the first place, all my messages just started being delayed a few days ago! I have to uncheck this box in every new message, which is very annoying. Please help! Thanks, -- Carlos I am having the same problem, it keeps ticking the box for 5pm. Can anyone help?? Carlos wrote: Outlook 2007 Delay Delivery Problem 03-Dec-09 I am having a problem wi...

Problems with outgoing e-mail
I am having problems sending e-mails out using my Outlook. I am using a high speed connection in my hotel room. The IT person from the hotel said it's because of their security system (spoofing!?!). I am able to use my browser just fine, Yahoo mail works fine, I receive mail in Outlook fine, but can't send any out The error I get is "The SMTP server name you specified cannot be found". The "My server requires authentication" is checked. I'm afraid it is not my Outlook, and the hotel can't do anything else I appreciate anyone's help. On Wed, 19 May 2004...

setting the status programmatically on a custom entity
Hello, I have the following code which closes a custom entity (5 being the value of the status reason associated with "Close" in the "Inactive" state). THis code is ran in a pre-update plugin. SetStateDynamicEntityRequest request = new SetStateDynamicEntityRequest(); request.Entity = new Moniker(); request.Entity.Name = "lund_cheevent"; request.Entity.Id = CHEEventID; request.State = "Inactive"; request.Status = 5; service.Execute(request); The problem is that I receive the following error: "State Code...

multiple colors on format of plot area
How can I change the format of the plot area to multiple colors. for example I would like to have a Bar Chart with the background plot area starting @ blue , then Green, then Red. Then have the temperature bars plot over this area. thanks You can do so by bringing a stacked column chart into the mix. Please see Jon's example below: http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html -- John Mansfield http://cellmatrix.net "BillO" wrote: > How can I change the format of the plot area to multiple colors. > > for example I would like to have a ...

how i can change default file format Outlook uses to save message
I my saving message in my inbox using "File->Save As" menu. "Save As.." dialog by default shows "Save As Type:" as "HTML". How I can change default file format Outlook uses to save messages to as "Outlook Message Format - Unicode" ?????? Rajkumar <Rajkumar@discussions.microsoft.com> wrote: > I my saving message in my inbox using "File->Save As" menu. > "Save As.." dialog by default shows "Save As Type:" as "HTML". > How I can change default file format Outlook uses to save messages...

Conditional Formatting with UsedRange Property for each Worksheet
I am getting an error indicated below in my code (Application-defined or object-defined Error, Run-Time Error '1004'). I want to have a bottome border line every 5 lines. Can someone explain to me why this portion of code will not work? Huge THANKS in advanced!!! Sub JobTracker1() Application.ScreenUpdating = False Call shUnProtect RowCount = 3 With Sheets("Archive") Do While .Range("L" & RowCount) <> "" myMonth = Format(.Range("L" & RowCount), "mmmm") Application.StatusBar = "Moving Sa...

Problem with comments
Have a strange problem with a computer I recently updated. I have been imaging computers in order to update several software items - one of which is Excel. The problem I have is with the users old excel documents which we transfer accross (Even those created with the same version). The workbooks contain many comments and these are unaffected - but when a user tries to add a new comment on an old sheet the comment will come up in very light grey and be impossible to read. If you create a new sheet in the same workbook comments are fine. How do you change comment colour for a sheet? It has st...

Conditional Formatting #49
Does anyone know how to apply conditional formatting to a row of cells (i.e. to create a digital dashboard) based on the value of a particular cell? Let's say you want to change the formatting of row 2 if A1 is greater than 10: 1. Select row 2. 2. Format > Conditional Formatting 3. Formula Is and enter: =$A1>10 4. Format as desired. HTH Jason Atlanta, GA >-----Original Message----- >Does anyone know how to apply conditional formatting to a >row of cells (i.e. to create a digital dashboard) based on >the value of a particular cell? >. > ...

log-in problem
Hi All, I apparently shut off the power to my computer the other night before windows had finished logging off. Now when I go to start My Money program it gives me the following message: Money has detected that you did not close the file "My Money" before exiting. Money now needs to verify the information in your file. If your file is large this may take a few nimutes. Do you want to do this now? If you click no, you'll need to open a different Money file. YES NO Send Error Report If I click Yes or No, it keeps saying the e-mail address or...

Problem refreshing subform
I'm having problems refreshing a local table in a subform. After selecting a new customer on the main form, my code is supposed to delete the records in the subform and inserts the current customer's records for display . The odd thing is that when I insert a breakpoint and step through the code line-by-line, the subform displays the new records just as I intended. But when I remove the break point, the subform remains on the previous customer. What is it about executing the code line-by-line that causes the subform to behave differently? "CDM" <CDM@discuss...

Conditional formatting assistance
Hey all, I was hoping one of you could point me in the right direction... I have a single sheet Excel document to which I want to apply some conditional formatting. Rows 1 and 2 are header information (date). Column 1 and 2 also display information that doesn't contain data (city). The sheet contains a list of broken items that I've fixed. When I've fixed the same thing twice, I want the cells containing that item to turn color. When it's entered a third time, turn a different color, and a fourth time, a final color. For example, I enter Chicago_washer_1 in cell C3. Thr...

how to setup quotionnaire format
please answer I'll take you mean questionaire == survey ? If so, have a look-see at Debra's page: http://www.contextures.com/xlForm01.html Excel -- Survey Form With Option Buttons (Techniques by Dave Peterson) There's 2 sample files you can download on the page -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lalitbogra" wrote: > please answer Re: how to setup quotionnaire format ...

Format DatePart
I'm trying to extract the month name (not just the number) from a ChkDate field in a query. ChkDate is a Date/Time data type. I use a DatePart expression which works fine to pull the month number. MonthNum: DatePart("m",[ChkDate]) But when I try to format that to get MonthName, it return Jan for any non- January month and Dec for any January month in ChkDate. Curious. Here's what I'm trying, and I can't see what's wrong according to syntax in help for Format and DatePart. Any ideas? MonthName: Format(DatePart("m",[ChkDate]),"mmm") Than...

Active Directory Problem
Hi Everyone, Hopefully you can help me out a bit here. Long story short, we had a failure last week that we were unable to recover Active Directory from. As such, we've had to rebuild AD from scratch - now the domain name and all user accounts are recreated and identical to the previous setup. However, I can no longer access CRM. I assume CRM 4.0 is authenticating on, not only AD membership, but based on an AD GUID of some sort - which I can't replicate. How can I get back in to re-load my users? Thanks, Craig Hi, Did u check if the users were part of CRM groups created ...

problem in SDI when I close application
Hi, I have made SDI application . But the program crashes in CDocument destructor when I close the application . In this statement: m_pDocTemplate->RemoveDocument(this); I havent changed generated code of document and view class of SDI application. I am unable to understand why it happens. Please help. Thanks, Dhaval Define "crash". There is no such thing. There are exceptions thrown due to access faults or similar errors, and they occur in some specific place in the code; there are assertion failures, which always indicate a line of code. Your description doe...

Problem in setting Substitutes fonts to CButton
I developed an MFC application. It is setting fonts properly. But it is not setting fonts for Substitutes fonts like Arial CE, Arial CYR in Windows 2000. Also same font is working fine in edit box. Please let me know if you have any solution to it. Regards, Ganapathi ...

New check format
I modified a copy of current check report, and trying to use this new format when I print a check. However, GP system didn't pick up the new format. What should I do to make them recognize each other? Thank you so much, Sophie Sophie, If you modified an existing check layout, you want to make sure to give users access to the modified check via Advanced Security or Security, depending on what your company uses. If the reports.dic is local and more than one user needs to use the modified check, you'll need to make sure each local reports.dic gets the modified check. Hope thi...

Excel 2000: Regional date problem
We'e just upgraded from Excel 97 to 2000 and are having problems because dates in spreadsheets are showing in US (mm/dd/yy) format rather than in European (dd/mm/yy). This si true whether I use a standard date format or make a custom format. I've checked the regional settings in Windows 2000, and they are correct. I thought Excel picked up the default from here. Any other ideas where I might look? Thanks in advance Alan I wasn't able to duplicate your problem, Alan. I switched to UK settings through Regional Settings. Then I opened Excel 97 and entered =NOW() in two ce...

Date format problem #2
Dear All, Please suggest me the solution of my problem, as i m handling data for 60,000 in no.Some body in data entry has enter the date in a cell in gernal format e.g 20.03.2003 tht is 20th march 2003, i wnt to convert in the date fomat,like 3/20/03.. i tried ..but i failed to tht please suggest some alternative solution for tht..becoz it is not possible to do it mannually waiting for the response Thanks NISHANT Nishant Try using Data/Text to Columns. and assign the information as a date. Andy. "Nishant" <nishant.khare@wipro.com> wrote in message news:090201c37ce0$1b...

Preserve format in pivot table
I have a pivot table where I formated the numbers using the "accounting" format. When I refresh the table, it goes back to the "general" format. How do I keep the "accounting" format when I refresh? Thanks Things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. Set the pivot table to preserve formatting: --On the pivot toolbar, choose PivotTable>Table Options --Add a check mark to 'Preserve formatting', click OK Depending on your version of Excel, you ...

labelflash problems
You'll need to copy the file gdiplus.dll l into your bin-directory.how do i do this Have you tried the support at labelflash? This .dll was included in Windows, have you looked on your disk? Help here http://msdn.microsoft.com/en-us/library/ms533798(VS.85).aspx -- Mary Sauer http://msauer.mvps.org/ "countryfella" <countryfella@discussions.microsoft.com> wrote in message news:0CA16383-2D50-4465-AF13-6F99962C607B@microsoft.com... > You'll need to copy the file gdiplus.dll l into your bin-directory.how do i > do this i have vista premium 32 bit os.i downl...

Problems Sending/Receiving Email
I am using Outlook 2000 SP-3. Suddenly I've been having problems sending/receiving email. When I click the send/receive icon nothing happens at all. Nothing gets sent or received and no dialog box or error message pops up. I've also tried sending/receiving through the Tools menu and nothing happens. It's as though I never tried to send the message at all. If I restart my computer it "works" again. But I'm having to restart my computer several times a day in order to be able to check my messages! Any ideas? "Elizabeth" <anonymous@discussi...

Problem with versions of word and outlook
Hi My access app uses word and outlook references which I can not get rid of due to certain features that my app has. I have this problem that I need to send me app to various clients who have varying versions of outlook and word and thus my app fails to run. Is there anyway my app can find the versions of outlook/word at start-up and readjust links to appropriate versions of outlook/word before continuing? If not, is there another way to fix this problem? Many thanks. Any help would be appreciated. I am really stuck. Regards Whoa: no need to include so many newsgroups! Consider u...

Vista/Access Database Network Problem
Has the very serious network problem in Vista covered by Article 935366 "Issues that you may encounter when you open or are using a Microsoft Office Access database on a computer that is running Windows Vista" been fixed by either Vista SP1 or Access SP3? I can't find any reference to a fix in these service packs?? To lose your data is indeed a very serious bug. Part of the article on 935366 states: When you open or are using a Microsoft Office Access database on a computer that is running Windows Vista, you may encounter one or more of the following issues: .. Access ...