VBA to change the text colour in a shape

[cjcass]

Hi, I have entered some VBA (by right clicking the wksheet tab) to change the fill colour of a shape based on a cell value, as follows...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$8" Then
Select Case Target.Value
Case Is = "Full"
ActiveSheet.Shapes("Rectangle 1").Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 118, 115)
End Select
End If


Q: What script would I need to type into this to also change the colour of the text in Rectangle 1, to say RGB(255, 255, 255)..?

Many thanks,
Chris

 

[cjcass]

Yes, I have a large spreadsheet with lots of 'button driven' macros within, other worksheets also have event codes in them - how could I check for interference? are event codes worksheet specific?
Rgds,

 

[Rick Rothstein]

Yes, I have a large spreadsheet with lots of 'button driven' macros within, other worksheets also have event codes in them - how could I check for interference? are event codes worksheet specific?

There are two kinds of event code... sheet specific (double-click a sheet name in the Project Window) and workbook wide (double-click the ThisWorkbook item in the Project Window). The sheet specific ones are, well, sheet specific, but if you have formulas on other sheets that include cell reference on a specific sheet, then that specific sheet's event code could theoretically influence happenings on the sheet with the cell references, especially I would think via the Calculate event. Workbook wide event code can definitely influence sheet specific event code. Just to prove to yourself that pasting works, open a new, blank workbook, put some rectangles on a sheet, put my event code in that sheet's code module and then copy/paste the words Full, Half, etc. from a foreign source and see if the rectangles are affected or not.

 

[cjcass]

Hi Rick,

I copied the code into a new spsht and it still didn't work!! The rectangles only update when I put the cursor into each cell and press return... I wonder if I have a problem with my settings in Excel??

Have checked my file and overall I have 3 worksheets with 'sheet specific' event codes in them - there are no formulas in any cells reading from any other wkshts in these wkshts or vice versa. I have no workbook wide event codes. Interestingly though, the file is now running slowly and every move I make (eg. just entering text into any cell) takes Excel a few seconds to calculate so I may have a wider problem and event code triggering is just one symptom. The file size is 8MB. I would email it to you but it's commercially sensitive.

Is there a way I can determine what is slowing the file from a calculation perspective?
Is there some code I can use in a macro to trigger the event code? or is there another option VBA-wise that I could adopt to bin the event codes and execute it in another way?

Many thanks for your enduring help with this!!

Best Rgds,
Chris

 

[cjcass]

Have just discovered that when you paste text into one cell only the change event triggers, but pasting into any more than one cell it doesn't - this seems to be the issue!!

 

[cjcass]

Hi Rick,
Just wanted to say thanks for your time on this - I found another solution in the end, but thanks again...
Rgds,
Chris