to change shape colour by conditional formating/ VBA

**asmi** · 11-02-2013, 01:16 PM

I have shape of a pump named "group 558" inserted in sheet 1. Cell B4 linked with another cell in sheet 2 and gets value either 1 or 0 if the pump is running or in stop position.

The requirement is colour of shape should change to green when cell B4 gets value 1 and changes to red when gets 0 (zero).

Colour changes happens as required when I enter the value 1 or 0 in cell B4 manually, but it fails when it takes the values via the cell link. How this problem can be resolved? thanks.

the code given is as follows

Please Login or Register  to view this content.

**protonLeah** · 11-03-2013, 03:09 AM

To attach a Workbook
(please do not post pictures of worksheets)

Click Advanced (next to quick post),
Scroll down until you see "Manage Attachments",
Click that then select "add files" (top right corner).
Click "Select Files" find your file, click "open" click "upload"
Once the upload is completed the file name will appear below the input boxes in this window.
Click "Done" at bottom right to close the Attachment Manager.
Click "Submit Reply"

**Greg M** · 11-03-2013, 12:21 PM

Hi there,

As written, the colour will change only if Cell "B4" happens to be the active cell - try the following code instead:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

**asmi** · 11-04-2013, 05:56 AM

Thanks, it works good.

If I want to link the active cell B4 to a cell in another work sheet, for eg. Sheet2, what change to be made in the code? Please help.

thanks

**Greg M** · 11-04-2013, 06:36 AM

Hi again,

Move (or copy as appropriate) the code to the VBA CodeModule of sheet "Sheet2" and change the following line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

(where "Sheet1" is the name of the worksheet which contains the Shape)

Hope this helps - please let me know how you get on.

Regards,

Greg M

**asmi** · 11-04-2013, 08:58 AM

Hi for immediate response.
Little confused, my shape is in sheet 1, active cell is B4 in sheet 1 and linked to cell A4 in sheet 2. Should I retain the code in sheet 1 as before and copy the code to sheet 2 with replaced line. Could you please explain? Better give code separately for both the sheets, if you don't mind. file attached.
thanks

**Greg M** · 11-04-2013, 09:10 AM

Hi again,

Place the following code in the VBA CodeModule of the worksheet "Sheet2"

Please Login or Register  to view this content.

You don't need to have any code in the VBA CodeModule of the worksheet "Sheet1"

Please let me know if this works for you.

Regards,

Greg M

**Solus Rankin** · 11-04-2013, 10:18 AM

Here is another alternative. This is untested since there isn't a sample worksheet to test it on.

Please Login or Register  to view this content.

**asmi** · 11-04-2013, 10:49 AM

Hi
It works perfectly, thanks a lot.

If I have more pumps(shapes) to be displayed on the same sheet 1 and the reference cells are in sheet 2(eg A4, A5, A6 etc), how this can be achieved? Now I have the shape name as "Group 558". I have similar shapes named "Picture 1", "Picture 2" etc. Then how the code will be changing. Should I write separate code for each shape?
thanks

**Solus Rankin** · 11-04-2013, 10:52 AM

If you are going to be adding more reference cells and shapes, I would change from the way the code is currently written, to a code using a select case syntax.

**Andy Pope** · 11-04-2013, 11:15 AM

If you use a transparent image then you can do away with code and simply use CF

**asmi** · 11-04-2013, 08:42 PM

Hi,
I have another problem. When I put 1 or 0 in the active cell or linked cell, it works. But as these numbers 1 or 0 are generated by system or by the excel formula, it doesn't work. The file is attached. The cell F8 gets 1 or 0 OR cell G8 have "run" or "stop". How this issue can be solved?

**Solus Rankin** · 11-04-2013, 11:14 PM

Please Login or Register  to view this content.

**asmi** · 11-05-2013, 04:13 PM

Hi

Thanks for the reply. Unfortunately it doesn't work. Could you please look into it and find a solution, file modified with new code attached

thanks

**Solus Rankin** · 11-05-2013, 04:45 PM

Because you're using a combobox and a formula the worksheet_change event is not being triggered. try

Please Login or Register  to view this content.

**asmi** · 11-05-2013, 06:28 PM

Hi

I doesn'work. That means the code will not work with combobox in the worksheet? Any other way to resolve this issue?

thanks

**Solus Rankin** · 11-06-2013, 01:20 AM

The code in post 15 worked fine for me on your sample document.

**asmi** · 11-06-2013, 12:12 PM

Hi,
Please find the attached file. When I enter "RUN" manually in cell G8, the cell F8 get the value 1 and it works. But when I select the equipment thru the combo box and the cell G8 get "RUN" or "STOP" string and cell F8 gets the required value 1 or 0, it doesn't work. Could you please look into it.

thanks

**Solus Rankin** · 11-06-2013, 01:53 PM

The your most recently attached workbook did not have the code from post #15. Please see attached file.

Note- the formatting is different from your original sample, though it doesn't seem to be causing any problems.

pump%20run%20indication%206(1).xlsm

**asmi** · 11-08-2013, 03:57 AM

Hi

It works perfectly. thanks

to change shape colour by conditional formating/ VBA

LinkBack

Thread Tools

Rate This Thread

Display

to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Re: to change shape colour by conditional formating/ VBA

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Excel - conditional formating on graphs. Change colour on a value

[SOLVED] conditional formating to change cell colour when time has reached 30 mins then 60 mins

Conditional Formating-change colour

Conditional Formating-change the cell colour to green

Conditional formating-change colour when D30 is equal

Bookmarks

Bookmarks

Posting Permissions