Conditional FormattingEOMONTH Test

Using Conditional Formatting I was trying to do an end of the mont
check on a preceding cell date entry to control whether or not the cel
after that one would show an orange border (the date entered in the cel
had to be less than or equal to the EOMONTH of that date to give th
subsequent cell an orange border).  I could do similar tests with othe
functions (e.g. MONTH, TODAY, Others) but when I tried to use EOMONT
I'd get an error message ("You may not use references to othe
worksheets or workbooks for Conditional Formatting criteria").  I wa
able to do pretty much the same thing by creating another column usin
EOMONTH that gave the end of the month date associated with the dat
entered in the preceding cell column.  However, I'd still like to kno
why EOMONTH wouldn't work within my Conditional Format Formula (I jus
started learning about the Excel Conditional Format feature).

MT

--
Message posted from http://www.ExcelForum.com

0
7/27/2004 3:29:36 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
440 Views

Similar Articles

[PageSpeed] 6

Hi
problem is that EOMONTH is part of the analysis Toolpak 
Add-in (which is a different workbook). But you can easily 
replace EOMONTH. e.g.
=DATE(YEAR(A1),MONTH(A1)+1,0)

gives you the last day of the month for the date value in 
cell A1


>-----Original Message-----
>Using Conditional Formatting I was trying to do an end of 
the month
>check on a preceding cell date entry to control whether 
or not the cell
>after that one would show an orange border (the date 
entered in the cell
>had to be less than or equal to the EOMONTH of that date 
to give the
>subsequent cell an orange border).  I could do similar 
tests with other
>functions (e.g. MONTH, TODAY, Others) but when I tried to 
use EOMONTH
>I'd get an error message ("You may not use references to 
other
>worksheets or workbooks for Conditional Formatting 
criteria").  I was
>able to do pretty much the same thing by creating another 
column using
>EOMONTH that gave the end of the month date associated 
with the date
>entered in the preceding cell column.  However, I'd still 
like to know
>why EOMONTH wouldn't work within my Conditional Format 
Formula (I just
>started learning about the Excel Conditional Format 
feature).
>
>MTM
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
frank.kabel (11126)
7/27/2004 3:42:48 PM
Reply:

Similar Artilces:

Conditional format question
What formula is suitable to determine if a value in 'B' is not present in A:A example: oranges are in B3 but can not be found anywhere in A:A for example =IF(ISNA(VLOOKUP(B1,A:A,1,FALSE))=TRUE,"not found",VLOOKUP(B1,A:A,1,FALSE)) Click yes if helped -- Greatly appreciated Eva "Gotroots" wrote: > What formula is suitable to determine if a value in 'B' is not present in A:A > > example: > > oranges are in B3 but can not be found anywhere in A:A If you're looking for a conditional formatting formula... =ISNA(M...

Excel Conditional Sum Wizard Totals
Operating System: Mac OS X 10.6 (Snow Leopard) Hi, <br><br>I've just loaded Excel onto my Mac and have been trying to do some conditional sums, however for some reason it will not allow me to display the totals on a separate sheet. It just keeps saving to the same sheet as the table, even when I select a cell on another sheet. Can someone please help advise how this is done? Or is this something that is not allowed? <br><br>Thanks What you 'want' shouldn't be a problem, although I'm not confident that I'm certain what that is & it's...

Test
Test Message. ...

Test
I�m a new user and would like to test whether this functions. ...

Help
Forgive me if "Conditional Sums" is not the accurate word for what I am trying to accomplish... I'm at a loss for what to call this situation. What I have is a spreadsheet totaling attendance figures for classes taught by two people (Dave and Cary). Column A lists their name, and next to it in Column B is the number of people who attended that class. What I need is a formula that will look at the sheet and report in a given cell the total for attendance for each person. I can work basic formulas like SUM, but that will give the total for the entire column, when what I n...

condition based on subform
Hi there, I'm trying to do something here and think (well I know) it's not working. Essentially I want some code to execute based on whether there are any records present on a subform. If there are no records present, code doesn't run and things continue on normally. If there are records present in the subform, then execute the code. Here's the code I wrote: Private Sub Form_Open(Cancel As Integer) If Me![sbfmMFGPkgDetails].NewRecord Then If Me![sbfmMFGPkgDetails]![HSTAT] = "I" Then Me.Inv.SetFocus Me.Inv = "Y" Me.quoteID...

testing
test "JohnA" <johnag@live.no> wrote in message news:01C49AB0-4B91-4DD0-B33B-E22CA6F771E2@microsoft.com... > test Test failed :( There are groups specifically for test messages. Please use one of those instead. ...

Script running isinteg -test for all mailbox stores on a server
Hi, Does anybody have a script for: 1. dismounting information stores 2. running isinteg for information store - output to a logfile 3. mounting information store again This would be a nice feature to run once and a while to check that everything is ok! Kind rgds Peter Jensen On Tue, 16 Aug 2005 10:33:17 +0200, "Peter Jensen" <peter.topp@mail.dk> wrote: >Hi, > >Does anybody have a script for: > >1. dismounting information stores >2. running isinteg for information store - output to a logfile >3. mounting information store again > >This would...

auto update of conditional macro
Hi again, I have just noticed that when I open the spreadsheet the cell with the =today() command automatically updates but the macro that is dependant on the answer does not update. I have tried to use refresh F9 but that doesn't work. I can go into the cell and hit enter and that then refreshes the colour assigned to the day. Is there a way of doing this when the spreadsheet opens up? Or is that not advisable? Andrew Are you using the worksheet_calculate event? That event recalced for me when I opened a workbook with =today() in a cell (xl2003). If you're not using that event,...

conditional format
I have a sheet with 6 columns of data. Starting from column B to G.I will like when there is data or text in all column cell for the color to change to say Red. And if all cells does not have data for color to start as it is. A macro will be great, since I will be run other macro on these data. Thanks in advance. Hi Sam! Taking row 1 as my base: Use condition formatting Formula Is =COUNTA(B1:G1)=6 Press Format Select Format OK OK -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) availa...

test #4
Test of the News -- Elvira Mi Photo Gallery http://www.personal.able.es/ensoriano ...

Excel 2007 Conditional Formatting Problem #2
Hi, I've got a little problem. I'm busy with a spreadsheat where i can see if someone has been paid on a sertain date. I've made a litte example-spreadsheat: http://www.savefile.com/files/1512005 The Problem: I want the numbers in the range C4:F7 to be colored green if it is paid and red if not and when there is a zero in the cell that there is no color.. in the range 17F:17I you can see if there has been paid on that day. I can put a conditional formatting in the range C4:C7 like: =0, no filling (stop when true) =17F="yes", green filling -17F="no", red filling...

Conditionally Converting Text to Numbers in VBA #2
It's ok thanks; solved it me'self on error resume next check = datevalue (cellref) if check > 0 then ' alter the date using datevalue else ' ignore end if on error goto 0 Thanks anyway ;) -- Jon ------------------------------------------------------------------------ Jon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3178 View this thread: http://www.excelforum.com/showthread.php?threadid=268772 ...

Multi-row conditional formatting help
Hi there, I want to format a row based on whether column E is greater than column D. I want this conditional formatting to apply to 7000 rows. I can make the row change colour for the first row, which is fine, but when I highlight all the rows and then select CF, and then click on OK, it makes all rows red, its still using the reference to the first rows cells. How do I make the CF apply to all rows? Cheers, Steve What version of Excel are you using? If you are using 2007, go to Conditional formatting and make a new rule from a formula. Put this formula in $E1>$D1 Set the for...

test #33
test ...

More that 3 conditions for conditional formating?
How do I set more that 3. I need 6 or 8. I want to have color of cell change if one of the 6 or 8 words appears in the cell. Thanks, Jerry limit is 3. You can, however, check for the appearance of the word by some formula like =NOT(ISNA(MATCH(A1,F1:F8,0))) where F1:F8 contains the list of 8 words and A1 is the active cell. If it's not an error, the word exists, and you can make the cell be whatever color you want. You just can't have 8 different colors. Bob Umlas Excel MVP "Jerry Kinder" <jkinder@jkinder.com> wrote in message news:eefsibF1FHA.664@tk2msftngp13...

How To Copy Conditional Formatting to Several (1,000) Cells
I get the whole Conditional Formatting thing, so I'm not looking for a tutorial. However, I can not figure this one out. What I need, is an individual row to be highlighted a certain color, depending on the content of one of its cells. Sounds easy enough right, well, I need to have this on about a thousand rows, and for some reason, when I enter K1 as the dynamic cell reference, instead of $K$1 as the static reference, it changes all thousand rows by whatever is in K1. . . Help me!! Thanks! You didn't post what formula you are using, so I'll just use an example: Selec...

Test
Test ...

CRM 4.0
Does anyone have any experience how to automate keeping Dev, Test and Production CRM 4.0 environments in sync both data and code? Is this more of an Integration Synch or via Deployment manager "Import Organization"? We mainly use the Deployment manager "Import Organization" to synch our dev, test and production environments since v4.0 makes this pretty easy. Frank Lee, Microsoft Dynamics CRM MVP http://www.workopia.com/Links.htm http://microsoft-crm.spaces.live.com "VSI" wrote: > Does anyone have any experience how to automate keeping Dev, Test and &...

conditional formatting #23
Hi, When i input value in A1 eg 100, then in B1 i set a formula =if(A1>99,today(),""), in B1, it returns to 4/12/2003 but i want to set this formula in B1 Format>Conditional Formatting>Formual Is> =if(A1>99,today(),""), it returns nothing, blank cell why? if the conditional formatting cannot satisfy my request. please suggest me another method or VB. thanks in advance. Raymond ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~No...

BLANK cell as a result of IF condition
I'm trying to find a way how to use IF condition: =IF(MyCondition; ValueIfTrue; ... else leave cell empty ...). =IF(MyCondition; ValueIfTrue; "") is not good for me, because it does not leave the cell empty. Thanks in advance for every suggestions. Vlado You can't. A formula must return a result, even if only an empty string as you have, so if you have a formula in a cell, the cell will not be empty. -- David Biddulph "Vlado Sveda" <VladoSveda@discussions.microsoft.com> wrote in message news:57EFEB43-F266-424E-BD2A-E19F44D189C3@micros...

Macro for test result Count as pass, fail and incomplete.
Hi all, Please send me a macro to resolve my issue as i have to excecute test cases and after that at the end of the day i need to send the number of count for my test cases as Pass, Fail and incomplete, and right now i'm doing this manually so please send me a macro so that just running that macro i 'll able to count my test result separatly as pass fail and incomplete... Thanx in advance... Amar You have given no information about the data layout, so it is difficult to be specific, but you can probably do what you want with a worksheet function instead of a macro. See Help fo...

How do I make a condition in Excel 2000 to print out the output?
Hello, I need some help, I wanted to make a condition so when a item is less then what it suppost to be I wanted it to turn red and at the end I would like a print out of all the item that are in red, or when the condition is met I would like a print out. Could someone help me out with this problem? I am trying to inventory some of my item in Excel for now then try to put it in a access form but for now I would like to setup the condition so I can have a good idea on how to do this. Could also someone give me a web site where I can make up some formala myself? Thank You Hi for coloring see:...

self adjusting pie chart segment colours based upon set conditions on the work sheet
I am trying work out a way in which to get pie chart segments to sho one of three colours based upon set percentage conditions on the hos worksheet for each segment value -- Message posted from http://www.ExcelForum.com Set up a range with your percentages. Say you wanted to identify <20%, 20-40%, and >40%. Put these numbers in the sheet: 0 0.2 0.4 And color the cells they are in with the color you want the wedge (for red below 20%, color the cell with 0 red). This way is easiest, because if you want to change the percentage cut offs, or the colors, you can easily change the she...

Hiding Rows Conditionally
Is it possible to hide a row only if a certain cell in the row contains a reference to another cell that has a specific value? Here's the scenario: Column A of a Summary sheet contains a list of 20 categories. Each category row contains references to data on another worksheet. Often, there is no data for a specific category, so the Summary sheet displays the category with zeroes or various errors depending on the formulas referencing the data. I would like to hide the entire row if the referenced data cell contains a certain value, for instance "0". Can it be done? Thanks for a...