I have a whole range of cells that require conditional formatting but for testing purposes have just the two. Cell E15 has a drop-down list which when one of the entries is selected, colours E16, the cell underneath it. The code for doing this is below. The issue is, it doesn't colour the cell below and it's probably some really daft mistake that I can't see.
Many thanks
ArtySin
Please Login or Register to view this content.
You need to define the variables as ranges and use Set:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I'm a little lost as to the layout you are using..
you pass source as a range and then have source.range("E15")?
I'm thinking the best way to do this would be with a worksheet change event with code like:
Needs to be in the sheet object within your vba project.Please Login or Register to view this content.
This uses a the Test macro to call the Projects procedure.
Please Login or Register to view this content.
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
Guys,
I've taken the above suggestions but still no go. It's in the code for sheet1 and I've altered it as per TMShucks post so that it now reads as below. Thanks for the help so far.
Please Login or Register to view this content.
You cannot declare the range variable Source twice in the same procedure. Remove the Dim statement.Originally Posted by ArtySin
Guys,
I've taken the above suggestions but still no go. It's in the code for sheet1 and I've altered it as per TMShucks post so that it now reads as below. Thanks for the help so far.
Please Login or Register to view this content.
Tried that AlphaFrog but still no go. I tried doing that and then also removing the "Dim Bar as Range" so that the code now reads as below but as I mentioned still no go.
Thanks in advance
Please Login or Register to view this content.
Did you try the code I posted? there doesn't seem anything wrong with the last bit of code you put. In what way doesn't it work? Are you expecting this to run "automatically" when you change a cell?
Now the question is how are you calling the Projects procedure? It's now expecting two range arguments passed to it. This code is being called by another procedure. The problem may be outside this procedure.Tried that AlphaFrog but still no go. I tried doing that and then also removing the "Dim Bar as Range" so that the code now reads as below but as I mentioned still no go.
Thanks in advance
Please Login or Register to view this content.
Also, don't just describe something as no go or doesn't work. If you could explain exactly what happens (error and description, wrong result, nothing) that helps us diagnose the issue.
Sorry Guys,
Yes I should have been more specific (slaps own wrist).
AlphaFrog, I've placed the code in the sheet1 and so expected it to work when I enter one of the values in E15. However, when I do enter one of the values nothing happens other than that value being entered in E16.
Yudlugar, when I run your code I am expecting it to update automatically and when I tried your code I get: Compile error, User defined type not defined amd it highlights the very first row: Private Sub Worksheet_Change(ByVal target As ref)
Many thanks
Oooooops should be byval target as range.... sorry!
Please Login or Register to view this content.
Guys, thanks VERY much for sorting this out for me. Both solutions now working so both please take a bow
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks