Another Simple GoalSeek question ??

[monir]

Hello;

How can I change the fill in the changingCell to, say, ColorIndex = 15, if
the goalSeek is successfull ??
Currently, I've in a loop:
Cells(myrow, colSet).GoalSeek Goal:=Cells(myrow, colVal), _
ChangingCell:=Cells(myrow2, colChange)
If GoalSeekVal = True Then Cells(myrow2, colChange).Interior.ColorIndex =
15

The color doesn't change. Apparantly, "GoalSeekVal" is not the correct
variable name in the GoalSeek method. It remains empty while the method is
successful. "True" is likely returned to something else!

Regards.

 

[paul.robinson]

Hi
Try this
Dim GoalSeekVal as Boolean
GoalSeekVal = Cells(myrow, colSet).GoalSeek(Goal:=Cells(myrow,
colVal), _
ChangingCell:=Cells(myrow2, colChange))
If GoalSeekVal = True Then Cells(myrow2,
colChange).Interior.ColorIndex = 15

or possibly this

Dim GoalSeekVal as Boolean
GoalSeekVal = Cells(myrow, colSet).GoalSeek(Cells(myrow, colVal), _
Cells(myrow2, colChange))
If GoalSeekVal = True Then Cells(myrow2,
colChange).Interior.ColorIndex = 15

untested
regards
Paul

 

[monir]

Paul;

Thank you for your reply.
It seems that the variable GoalSeekVal may or may not be declared as
Boolean, but the method arguments must be included in parentheses if the
GoalSeek return value is assigned to a variable (GoalSeekVal in my example).
However, when using parentheses, including the names of arguments is optional.

Dim myChngCell As Range
Set myChngCell = Cells(myRow2, colByChange)
GoalSeekVal = Cells(myRow, colSet).GoalSeek(Cells(myRow, colToVal),
myChngCell)
If GoalSeekVal = True Then myChngCell.Interior.ColorIndex = 15

I concur with MrExcel Emma's observation: " GoalSeek seems to return False
if ChangingCell already has the correct value. Which could be annoying.".

Regards.