help with conditional formatting.

matelot2

New Member
Joined
Feb 1, 2012
Messages
36
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--> I am using excel 2003. Part of office professional 2003.
I am trying to get a number in box H43 to go to red if the contents of box E23 is higher and go to green if box 23 is lower or same.
I highlight box H43
In conditional formatting
<format conditional="" formatting=""> Format/conditional formatting
I put
Condition 1
Cell value is less than or equal to E23
AaBbCcYyZz
Condition 2
Cell value is greater than E23
AaBbCcYyZz

The conditional box always changes to ="E23" but I suppose this is the correct format for the condition?
If I try to change it, it just adds extra “” around it.
I have checked that both cells have
<format cells="" number=""> Format/cells/number
set to number with ‘0’ decimal places.
Cell H43 stays green no matter what else I do, even if I manually change the contents of box E23 to a number lower or higher than the contents of box H43, the contents are just a number I have entered and not a formula from somewhere else.
Can anyone see what I am doing wrong?

<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if !mso]>******** classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--></format></format>
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--> I have clicked on the conditional formatting box at the top right of the conditional formatting box and the rest of the box has disappeared leaving me with just that entry box, I clicked on box E23 and the computer entered =$E$23 in the box so now I have =$E$23 instead of just ="E23", the contents of box H43 have now gone to red and won't change no matter what I enter into box E23?

<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->
 
Last edited:
Upvote 0
matelot32, Good afternoon.

They are simple rules and should not present problems.

I assume you're using the automatic rule lines.
In this case should not appear the result you told us.

If you are using the formula line for the rule and typing E23, then automatically Excel will place = "E23"

In this line you should always start with the equal sign =

Rule 1
= H43 <= E23

Rule 2
= H43> E23

Try it that way and let us know if it worked the way you need it.
I hope I've helped.
 
Upvote 0
I tried the same thing with two other boxes and it worked fine?
The only difference in the formatting of the boxes is I had changed D23 and H43 to number, I prefer text as I sometimes use slash as in 10/10. When I changed E23 and H43 back to text it all worked fine?
Perhaps it only works when the format is text and the location has $$'s in it?
So now I have =$E$23 in both condition boxes.
Just tested again and now it works with =E23 in both boxes? So I don't know what I have changed? I will keep looking.
 
Last edited:
Upvote 0
Yes you can use CF, but from 2007 they made a lot of changes to it, which will not be available to you.
Without being able to see your workbook, it's difficult to know why you were having problems.
 
Upvote 0
I am still having problems.
If I try to start a new one or alter a box from the start sometimes it works, sometimes it doesn't. If I copy and paste the good one I have it works ok but I can see no differences?
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top