VB code + Data Validation query

[Jennyc114]

Hi there,

I am trying to assign data validation to a cell in a spreadsheet using VB code, which as i understand is quite simple. I would also like to create a 'warning' system, which returns a symbol to indicate whether or not the validation has been met. For example, a red circle in the adjacent cell if the validation has not been met, or a green circle if it has. Is there a code which would meet these requirements? Or could you advise me as to a better way of doing this?

Thanks,

Jenny

[MattShoreson]

what about using conditional formatting instead of data validation?

[Jennyc114]

what about using conditional formatting instead of data validation?

Ok thanks, what does conditional formatting do? what code do you use to apply it?

[Bob Phillips]

Creating a macro is simple. Turn the macro recorder on and do it manually,
you will have a macro.

The circle seems harder, but as DV can be setup so that invalid values
cannot be entered, why bother?

--
HTH

Bob Phillips

"Jennyc114" <[email protected]> wrote
in message news:[email protected]...
>
> Hi there,
>
> I am trying to assign data validation to a cell in a spreadsheet using
> VB code, which as i understand is quite simple. I would also like to
> create a 'warning' system, which returns a symbol to indicate whether
> or not the validation has been met. For example, a red circle in the
> adjacent cell if the validation has not been met, or a green circle if
> it has. Is there a code which would meet these requirements? Or could
> you advise me as to a better way of doing this?
>
> Thanks,
>
> Jenny
>
>
> --
> Jennyc114
> ------------------------------------------------------------------------
> Jennyc114's Profile:
http://www.excelforum.com/member.php...o&userid=24908
> View this thread: http://www.excelforum.com/showthread...hreadid=384466
>

[Jennyc114]

My spreadsheet is automatically updated by a macro to pull data from an online source. Differences between this actual data and predicted data is then calculated by the spreadsheet, and i need to create a warning system to alert the user of whether or not the differences are outwith a certain range. It would be more user-friendly, because if the data validation disallowed invalid data it would disrupt the macro and prevent it from running. As far as i am aware, if you turn the macro recorder on, it will record the code for applying data validation, but then there is now way of telling it that you are colouring the circle green because the validation has been met or red if not. I was therefore asking if there was a code which sorted this?

Jenny

Creating a macro is simple. Turn the macro recorder on and do it manually,
you will have a macro.

The circle seems harder, but as DV can be setup so that invalid values
cannot be entered, why bother?

--
HTH

Bob Phillips

"Jennyc114" <[email protected]> wrote
in message news:[email protected]...
>
> Hi there,
>
> I am trying to assign data validation to a cell in a spreadsheet using
> VB code, which as i understand is quite simple. I would also like to
> create a 'warning' system, which returns a symbol to indicate whether
> or not the validation has been met. For example, a red circle in the
> adjacent cell if the validation has not been met, or a green circle if
> it has. Is there a code which would meet these requirements? Or could
> you advise me as to a better way of doing this?
>
> Thanks,
>
> Jenny
>
>
> --
> Jennyc114
> ------------------------------------------------------------------------
> Jennyc114's Profile:
http://www.excelforum.com/member.php...o&userid=24908
> View this thread: http://www.excelforum.com/showthread...hreadid=384466
>

[MattShoreson]

Assuming you are displaying all values whether they pass validation or not.

e.g.
A1 contains true
A2 contains false

in B1 use the menu format>conditionalformatting.
dropdown box select formula is - =A1=TRUE and select format

click add
and then dropdown box select formula is - =A1=FALSE and select different format.

Colour of cell will alter dependant on value.
NB This formatting can also be created via code.

[Bob Phillips]

Look up 'Highlight cells that meet specific conditions' in the Answer
wizard in help

--
HTH

Bob Phillips

"Jennyc114" <[email protected]> wrote
in message news:[email protected]...
>
> MattShoreson Wrote:
> > what about using conditional formatting instead of data validation?
>
> Ok thanks, what does conditional formatting do? what code do you use to
> apply it?
>
>
> --
> Jennyc114
> ------------------------------------------------------------------------
> Jennyc114's Profile:
http://www.excelforum.com/member.php...o&userid=24908
> View this thread: http://www.excelforum.com/showthread...hreadid=384466
>