Access Conditional formatting

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,814
Office Version
  1. 365
Platform
  1. Windows
I have a bunch of fields on a form where I entered conditional formula

"Check values in the current record or use and expression
Format on cells where the:
Field Value is greater than 0


Then the formatting is set to make the fore color red

if the value, which is calculated, results in 0 it stays black, if its greater than 0 it turns red BUT if its a negative number (like -1) it turns red. negative 1 is less than 0 so it should stay black. What am I doing incorrectly?

I tried "0" which not difference on results
 
Last edited:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,069
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Click on the Refresh Button and see if that makes a difference.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
871
Does the same for me in 2007.

However if you change it to Expression Is and then the formula like [txtColour]>0 it will do as you wish.

HTH
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,814
Office Version
  1. 365
Platform
  1. Windows
When you state (txtColor] do you mean a color or are you saying to you want like [Red]>0?

if I but [textcolor]>0 and then go below that section and select red from the color pallet nothing happens when the condition is met
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
871

ADVERTISEMENT

No, [txtColour] is the name of the control you are checking to see if the value is > 0
I just created a form and put one control on it called txtColour.
 

apr pillai

Board Regular
Joined
Aug 12, 2008
Messages
236

ADVERTISEMENT

Expression Is sgn([TextColor])<=0 - Black

Expression Is sgn([TextColor]) > 0 - Red
 

apr pillai

Board Regular
Joined
Aug 12, 2008
Messages
236
not seeing any different. can you please explain me again.

The expression will check the sign of the value in the field.
If the value in the field is negative it will evaluate to -1
if field value is Zero then sgn() function will return 0

if the field value is a positive number then sgn() will return +1

Based on this three returned values (-1, 0 or 1) you can define the color of the field value you want.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
871
What benefit is there of adding extra code of the sgn function when testing the value will do the same?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,587
Members
414,460
Latest member
uctc

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
Top