Conditional Formatting Based On Content Of Another Cell

[Guest]

I have this cell AN 11 who's content is not to be either 0 or "" if the content of cell AS11 has a value greater then 0 or "". If cell AS11 is say 280, then, cell AN11 cannot be equal to or less than, or event greater 280. If no value is entered into cell AN11 turn the color to RED and display a error message informing the user that AN11 cannot be left blank = "" or 0.

This, I know can be done using the conditional formatting and data validation features. The question, is how to do this. Any assistance or guidance will be appreciated. Thanks for your time..........

 

[Frank Kabel]

Hi Jay
not really sure about your condtions. So if you enter anything in AS11
you say that AN11 is not allowed to contain a value but in the next
sentence you also want an error message in this case?

 

[Guest]

Frank:

Sorry, for the confusion. The current formula looks something like this: =If(B11<>" "(AN11<=0,AS11>0)). Basically, if a record exists here, and AS11 contains data, then AN11 cannot be 0=empty/ " "=blank, AN11 too must contain data. AS11 is a result of calculated cell’s, AN11 is a plug (a manually entered value). AN11 must by the nature of things must have a corresponding value greater than 0 or " ". If no value is entered in AN11, then alert the user of the missing data by turning the background color of AN11 to RED.

However, if there is no record here (no new or next record) and AS11 is 0=empty / " "=Blank, then, leave the background color of cell AN11 as is.

Oh, I use " " as a representation of blank (empty string), instead of "" because this excel data gets imported into Ms Access 97 / Access XP later on for further analysis, and access does not know what to do with "" during the import process.

 

[Guest]

Frank:

Sorry, for the confusion. The current formula looks something like this: =If(B11<>" "(AN11<=0,AS11>0)). Basically, if a record exists here, and AS11 contains data, then AN11 cannot be 0=empty/ " "=blank, AN11 too must contain data. AS11 is a result of calculated cell’s, AN11 is a plug (a manually entered value). AN11 must by the nature of things must have a corresponding value greater than 0 or " ". If no value is entered in AN11, then alert the user of the missing data by turning the background color of AN11 to RED.

However, if there is no record here (no new or next record) and AS11 is 0=empty / " "=Blank, then, leave the background color of cell AN11 as is.

Oh, I use " " as a representation of blank (empty string), instead of "" because this excel data gets imported into Ms Access 97 / Access XP later on for further analysis, and access does not know what to do with "" during the import process.

 

[Frank Kabel]

Hi
still not totally clear. Best way: provide some examples
for each of your used cells and desccribe your expected
result.
Also explain what kind of data you expect: only numeric
values or is also a text entry possible in AN11 or AS11

-----Original Message-----
Frank:

Sorry, for the confusion. The current formula looks

something like this: =If(B11<>" "(AN11<=0,AS11>0)).
Basically, if a record exists here, and AS11 contains
data, then AN11 cannot be 0=empty/ " "=blank, AN11 too
must contain data. AS11 is a result of calculated cellâ?
Ts, AN11 is a plug (a manually entered value). AN11 must
by the nature of things must have a corresponding value
greater than 0 or " ". If no value is entered in AN11,
then alert the user of the missing data by turning the
background color of AN11 to RED.

However, if there is no record here (no new or next

record) and AS11 is 0=empty / " "=Blank, then, leave the
background color of cell AN11 as is.

Oh, I use " " as a representation of blank (empty

string), instead of "" because this excel data gets
imported into Ms Access 97 / Access XP later on for
further analysis, and access does not know what to do
with "" during the import process.

 

[Guest]

Frank:

Again, sorry for the confusion. Only numeric values. If AS11 value is 280, then the resulting entered data in AN11 may be less or even greater than 280. However, AN11 cannot/should not be left with no value " " or 0. Due to the fact that further calculation depend on these two cell's containing a value greater than 0 or " ". Calculations based on 0 or " " result in (as you know) all sorts of ugly error messages. Smile!

So, I'd like to prompt the user to enter a numeric value in cell AN11 by turning it's background color to RED if no value is entered. But, if there is/are no records leave all cell's backgrounds as they are without changing anything.

Hope this helps...........

 

[Frank Kabel]

Hi
o.k. then lets try the following:
1. Cell AN11:
- select this cell
- goto 'Format - Conditional format'
- enter the following formula
=(AN11="")*(AS11<>"")
- choose a red format
Note: Don't use " " as blank but alsways use "" as a
result for a blank cell

2. Select cell AS11
- Enter a validation for olny accepting numbers >0. e.g.
enter a formula like

-----Original Message-----
Frank:

Again, sorry for the confusion. Only numeric values. If

AS11 value is 280, then the resulting entered data in AN11
may be less or even greater than 280. However, AN11
cannot/should not be left with no value " " or 0. Due to
the fact that further calculation depend on these two
cell's containing a value greater than 0 or " ".
Calculations based on 0 or " " result in (as you know) all
sorts of ugly error messages. Smile!

So, I'd like to prompt the user to enter a numeric value

in cell AN11 by turning it's background color to RED if no
value is entered. But, if there is/are no records leave
all cell's backgrounds as they are without changing
anything.