Greetings,
I have been trying to get the following macro to work with my particular requirement and have had no luck thus far. I am a macro novice and any assistance would be greatly appreciated. I am trying to insert a picture in a weight tracking spreadsheet based on the value of another cell and whether it is greater or lower than the previous days weight. For example:
[TABLE="width: 447"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Weight[/TD]
[TD]Change[/TD]
[/TR]
[TR]
[TD]Monday, July 23, 2012[/TD]
[TD]217.6[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]Tuesday, July 24, 2012[/TD]
[TD]217.4[/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]Wednesday, July 25, 2012[/TD]
[TD]216.4[/TD]
[TD]1.0[/TD]
[/TR]
</tbody>[/TABLE]
I have a formula in column C (Change) that tracks the difference between days - =IF(B2-B3=B2, "", B2-B3)
I also have conditional formatting in column D that changes the fill color of the cell to red if the day's weight is higher than the previous day's weight, green for lower, and yellow for the same.
I have two jpg images, one a red arrow up, the other a green arrow down, that I would like to insert in the D column on the row corresponding to the day's weight either going up or down.
The way I see it, there are two ways to format the conditional statement, either compare today's weight value (column B, Weight) to the previous day's and if it is greater, insert red, lower would be green, no change would be yellow. Or if the Change cell for the day was positive, insert green, negative would be red, no zero would be yellow.
I have located a macro that does a picture insert, but I do not understand enough about the coding of such things to modify it for my needs. I have included this macro from the site [Excel] A macro to insert pictures in hopes that it can serve as a starting place for someone more knowledgeable than me.
Sub Picture() * *
Range("A6").Select 'This is where picture will be inserted * *
Dim picname As String * *
picname = Range("B6") 'This is the picture name * *
ActiveSheet.Pictures.Insert("C:\Users\vbayat\My Documents\vidabayat\re-market\" & picname & ".jpg").Select 'Path to where pictures are stored * *
''''''''''''''''''''''''''''''''''''''''''''''''''''''' * *
' This resizes the picture * *
''''''''''''''''''''''''''''''''''''''''''''''''''''''' * *
With Selection * *
.Left = Range("A6").Left * *
.Top = Range("A6").Top * *
.ShapeRange.LockAspectRatio = msoFalse * *
.ShapeRange.Height = 100# * *
.ShapeRange.Width = 80# * *
.ShapeRange.Rotation = 0# * *
End With * *
Range("A10").Select * *
Application.ScreenUpdating = True * *
Exit Sub * *
ErrNoPhoto: * *
MsgBox "Unable to Find Photo" 'Shows message box if picture not found * *
Exit Sub * *
Range("B20").Select * *
End Sub
Again, any help or guidance that can be offered is much appreciated.Regards,-BOB
I have been trying to get the following macro to work with my particular requirement and have had no luck thus far. I am a macro novice and any assistance would be greatly appreciated. I am trying to insert a picture in a weight tracking spreadsheet based on the value of another cell and whether it is greater or lower than the previous days weight. For example:
[TABLE="width: 447"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Weight[/TD]
[TD]Change[/TD]
[/TR]
[TR]
[TD]Monday, July 23, 2012[/TD]
[TD]217.6[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]Tuesday, July 24, 2012[/TD]
[TD]217.4[/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]Wednesday, July 25, 2012[/TD]
[TD]216.4[/TD]
[TD]1.0[/TD]
[/TR]
</tbody>[/TABLE]
I have a formula in column C (Change) that tracks the difference between days - =IF(B2-B3=B2, "", B2-B3)
I also have conditional formatting in column D that changes the fill color of the cell to red if the day's weight is higher than the previous day's weight, green for lower, and yellow for the same.
I have two jpg images, one a red arrow up, the other a green arrow down, that I would like to insert in the D column on the row corresponding to the day's weight either going up or down.
The way I see it, there are two ways to format the conditional statement, either compare today's weight value (column B, Weight) to the previous day's and if it is greater, insert red, lower would be green, no change would be yellow. Or if the Change cell for the day was positive, insert green, negative would be red, no zero would be yellow.
I have located a macro that does a picture insert, but I do not understand enough about the coding of such things to modify it for my needs. I have included this macro from the site [Excel] A macro to insert pictures in hopes that it can serve as a starting place for someone more knowledgeable than me.
Sub Picture() * *
Range("A6").Select 'This is where picture will be inserted * *
Dim picname As String * *
picname = Range("B6") 'This is the picture name * *
ActiveSheet.Pictures.Insert("C:\Users\vbayat\My Documents\vidabayat\re-market\" & picname & ".jpg").Select 'Path to where pictures are stored * *
''''''''''''''''''''''''''''''''''''''''''''''''''''''' * *
' This resizes the picture * *
''''''''''''''''''''''''''''''''''''''''''''''''''''''' * *
With Selection * *
.Left = Range("A6").Left * *
.Top = Range("A6").Top * *
.ShapeRange.LockAspectRatio = msoFalse * *
.ShapeRange.Height = 100# * *
.ShapeRange.Width = 80# * *
.ShapeRange.Rotation = 0# * *
End With * *
Range("A10").Select * *
Application.ScreenUpdating = True * *
Exit Sub * *
ErrNoPhoto: * *
MsgBox "Unable to Find Photo" 'Shows message box if picture not found * *
Exit Sub * *
Range("B20").Select * *
End Sub
Again, any help or guidance that can be offered is much appreciated.Regards,-BOB
