Hello all,
I want to conditional format a range of cell in "Short Date" or "Standard" if the condition is "Day" or "Week".
When I select "Day" the format is OK, but when I switch to Week the format stays in "Short Date". I need to press F2 then enter to see the number format.
Any clue ?
Regards
Works Ok in Excel 2010
Have you set 2 Conditions (1 for Day and 1 for Week) and not just assumed one is the default?
Cyrilz
I also have it working ok in 2007
I have entered 2 lines in the Conditional Formating
=$H$1="Day"
=$H$1="Wk"
and set appropriate formats for each
"dddd dd mmm yy"
"dd mmm yy" etc
I haven't checked "Stop if Valid" box
Do you have calculations turned to Manual
If that doesn't work Clear Contents of the cells with dates and set Display Format to General, then Redo Conditinal Formating
Hello Hui,
I've based the formating against the value of the cell (< 30000 or > 30000)
but now I've changed to your way (which seems better with less calculations).
Format 1 is "dd/mm", format 2 is "Standard."
Now the behaviour is a bit different :
When I select "day" I have the date format (which is ok)
When I select "Week" I have the date format. I press F2 then enter it switches
When I select back "Day", the format is "date"... (repeat then).
I've also checked that the format in the cells is Standard by default.
ANy (other) clue :-) ?
Cyril
Still works ok for me, the way I have interpreted how you have described it, and still No need for F2
Can you type out your rules exactly as you have then in Conditional Formatting or
post a small example of what your problem is somewhere
Hello Hui,
Here's my scrennshots (in French, I can't translate screenshots in English :-)
Starting point : Planned / Days
http://dl.dropbox.com/u/4900864/Gantt000.jpg
Switch to Weeks ==> Wrong format (as shown in img 002)
http://dl.dropbox.com/u/4900864/Gantt001.jpg
http://dl.dropbox.com/u/4900864/Gantt002.jpg
Conditional format rules :
http://dl.dropbox.com/u/4900864/Gantt003.jpg
Press F2 in cell and ENTER :
http://dl.dropbox.com/u/4900864/Gantt004.jpg
http://dl.dropbox.com/u/4900864/Gantt005.jpg
Thanks in advance if you see any mistake :-)
Cyril.
Hello Hui,
Here's my screenshots (in French, I can't translate screenshots in English :-)
Starting point : Planned / Days
http://dl.dropbox.com/u/4900864/Gantt000.jpg
Switch to Weeks ==> Wrong format (as shown in img 002)
http://dl.dropbox.com/u/4900864/Gantt001.jpg
http://dl.dropbox.com/u/4900864/Gantt002.jpg
Conditional format rules :
http://dl.dropbox.com/u/4900864/Gantt003.jpg
N5 contains "Week" or "Day", and is compared to reference cells.
Press F2 in cell and ENTER :
http://dl.dropbox.com/u/4900864/Gantt004.jpg
http://dl.dropbox.com/u/4900864/Gantt005.jpg
Thanks in advance if you see any mistake :-)
Cyril.
Cyril
Not sure exactly what is wrong as I can't read french or see the entire formulas
but it looks like there may be a clash in your logic.
CF: Line 1 is Value = si(N5)
CF: Line 2 is Formula N5=P1 and it will stop if true and not process rule 3
CF: Line 3 is Formula N5=Q1, will not be processed if line 2 is True and should be the same as line 2, ie P1 or vise versa
The following works for me
http://i48.tinypic.com/2sb01gw.png
Hello Hui, and thank you.
P1 contains "Day" and Q1 contains "week" since they are used for data validation (list) in N5.
I've changed the 3rd line to format Number (0 decimal). Nothing changes. But I've noticed that wherever I refresh a cell correct formating appears.
Could this be because I've got plenty formulaes in my sheet and XL has difficulties refreshing ?
Cyril
The size of the spreadsheet shouldn't matter
Why do you need the 3rd rule?
Is Calculation set to Manual or Automatic?
Can you post the spreadsheet somewhere?
This works for me as per your details
http://i45.tinypic.com/2n84rie.png
Here it is :
http://dl.dropbox.com/u/4900864/ganttSample.xlsm
Note : I removed all sensible data ;-)
Cyril
Firstly, That is a great looking dashboard, Well Done.
Secondly, That is a bizarre issue that is happening with Conditional Formatting
It looks like it is struggling to Change the Number format from Date to Number or back again. Which is the whole idea of Conditional Formatting ?
But as always there is a workaround ! ! !
Delete all the Conditional formating in the Range K6:AJ6
Copy and paste the following code in the code page for the "Tableau de Bord" worksheet in the VBA dialog
Subscribe
Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("Tableau de Bord").Range("N5").Value = "jour" Then
'
Range("K6:AJ6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Selection.NumberFormat = "dd/mm"
Else
'
Worksheets("Tableau de Bord").Range("K6:AJ6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Selection.NumberFormat = "#,##0"
End If
For Each c In Worksheets("Tableau de Bord").Range("K6:AJ6")
If c.Value = Worksheets("Tableau de Bord").Range("X3").Value Then
With c.Interior
.Pattern = xlSolid
.PatternColor = 0
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
End If
Next
End SubHello Hui,
thanks for your kind comments, it's always a pleasure to hear this from you (and Chandoo of course :-)
Nice idea, but I've avoided macros from now on (apart the DonneSemaine function).
But I'll give this a try, adding the following test :
If Target.Address() = "$N$5" Then
...
End Ifin order to avoid unnecessary computations when any cell changes.
Cyril
Cyril
I'm going to try this out tonight with both XL2003 and 2010 and let you know what happens, but I suspect it is a bug.
Hui,
Did you mean the behaviour of my worksheet ?
If yes --> I use XL2007 iferror() function regularly in my sheets, so I don't expect it to work in 2003.
If not --> I add the IF test because I suspect the function xill be called each time a cell changes.
No I will test the Conditional Format problem in both versions
Update: The Conditional formatting problem has been solved in 2010, so there must have been a problem with Conditional Formatting in 2007.
@Hui & @Cyril... I have noticed this peculiarity earlier too.
I debugged it for a while and then eventually gave up and used TEXT() formula to resolve it.
See this example:
=if(a1="short",text(datevalue,"dd-mmm"),text(datevalue,"dddd dd mmm yy"))
ofcourse, this would make the values in cells as text (so if you have any connected formulas that expect dates in the cells, you should use source dates as refs for them).
@Chandoo
The cells are used extensively elsewhere. It could be worked around, but would add to the complexity of the dashboard.
It is a very neat Dashboard which Cyril should be proud of.
Update 2: The Conditional formatting problem wasn't there in 2003 as 2003 never had the facility to vary the number formats in Conditional Formatting.
So it look like a problem with the implementation of Number Formats in Conditional formatting in XL 2007, which has subsequently been fixed in 2010.
@Cyril
A Simple Solution, without Macros
In the range K6:AJ6 use the custom number format
[>=30000]dd/mm;[<30000]##
30,000 is some time in 1982
and just use the Conditional Format which can do the colors Ok
This will still allow the following cells to function as normal
@Chandoo & @Hui : Many many thanks to both of you. You are opening me new sights.
I've never thought about number formating but this will be great for both 1/ correcting the bug and 2/ improving speed.
I've been largely inspired by Fernando's work for the Gantt diagram and tweetboard, rewriting completely the "calculations" sheets to fit my needs...
>> Note the custom format works well, speeding up the sheet refresh as expected (I had a problem with scrollbars if I click to fast, they repeatedly scroll, now it's OK with a fast PC).
@Hui again : the board was OK in 2003, since I used it (with the same conditionnal format --> see Fernando's) before switching to 2007.
You must log in to post.
Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.
