Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Rainhard Fischer   on Jan 17 In MS Office Category.

  
Question Answered By: Alisha Johnson   on Jan 17

The main problem is that every time you store a value into AQ you are
generating a new change  event, which again decides to put something in AQ,
which generates a new change event, etc.

You need to make sure that you only run the code if the change is for one of
the test columns. In my version of the code, I'd left I where it was, but
moved AI down to J so I could see it on the same screen. Hence my test
became

If Target.Column = 9 Or Target.Column = 10 Then

A couple of extra notes ...

You shouldn't pop up an error when you put something in column  I but haven't
got around to filling AI yet. I don't know what test you'd use for this,
but it's very unfriendly to work with as it is.

You set  the background red for an unacceptable value, but never set it back
to transparent when there is an acceptable value, so it stays red even when
OK.

The colon after the Else is stylistically wrong and will potentially result
in confusion when reading the code later. The rest of that line should be
moved to the next line ...

> Else
> Range("A" & row, "AR" & row).Interior.Color = RGB(255, 0,
> 0) 'If not any of those things, turn row  RED
> response = MsgBox("The following is a list  of acceptable values
> for the Rep/OSR field  when the supplier is BIGsupplier" & vbCr _
> & "The value you have entered  is not one of these allowed
> values. Please change this value.", vbOKOnly)

It is not good practice to use .FormulaR1C1 to store a value into a cell.
It could well be transformed if it looks like an R1C1 expression. To store
a value to a cell, use the .Value attribute; to store a "normal" formula,
use .Formula; to store an R1C1 formula, use .FormulaR1C1.

Share: 

 

This Question has 17 more answer(s). View Complete Question Thread

 


Tagged: