Joining two VBA Range Excel 2007 codes

  • Good evening

    I have a code in excel, which I would like to add another, but I can't, it gives an error, can you help me, please?

    Thank you


  • Move how and where?

    I've moved under: Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    And I still get an error.

    It would be easier to post the full code so I can understand, please.

    Thank you

    Edited 2 times, last by Bikke ().

  • I probably explained myself badly.

    The first VBA code works perfectly on its own and with the one below, but the VBA code below is the one that gives an error, so I'm saying that it should be joined, maybe it's not necessary, but what is certain is that it always gives an error.

    That's why I'm asking for help.

    I apologise for explaining myself badly.

    Best regards

  • Could you attach a copy of your file? It would be easier to see how your data is organized and to test possible solutions. Include a detailed explanation

    of what you want to do using a few examples from your data and referring to specific cells, rows, columns and sheets. De-sensitize the data if necessary.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Move the line:

    Dim lTarget As Range

    before the line (on top):

    Private Sub Worksheet_Change(ByVal Target As Range)

    Doesn't this make some sense ?

    Instead, do I need to say: Cut the line of code Dim lTarget As Range and Copy it as first line in the sheet's vbe module.

  • Hi Bikke - taking a quick look at your workbook, the problem I think is being caused by the worksheet Existencia being protected.

    Your code works fine if the sheet is not protected. Assuming your sheet protection password is "blank", the following should work:

  • Good evening gijsmo

    I apologise for opening the post again, but I didn't foresee something, and now I'm working with the file and I've noticed my mistake.

    Is there any possibility of adding something to the existing VBA code that prevents/leaves the original colour?

    Because when I click on one or more cells that have a colour, when I click on it I get the colour defined in the code (in this case yellow), but when I click on another cell, the colour that was there disappears and I get white, when in fact the colour was something else.

    I don't know if I'm explaining myself correctly.

    But the file already contains two examples of what I'm trying to explain.

    Is that possible?

    Thanks in advance, and sorry to open this post again


  • It is theoretically possible and would be relatively straightforward if all the original background colours were the same.

    One way would maybe be to create a matrix of all the cell colours in the table when the workbook is opened and then use that as a reference when restoring the background colour.

    However, you also seem to have conditional formatting some of which changes the background colour and this will probably override your Worksheet_SelectionChange code ie, you won't see the colour change in the cell you select if the conditional formatting rule applies to the cell.

  • Good evening gijsmo

    I put two colours just to give you an idea, but in reality it's only one, and that's what I want, nothing more,

    If it's possible to realise what I want, it would be wonderful and I'd appreciate the help.

    Thank you in advance for your attention.

    Best regards

  • If it's just the same colour in each cell then you should only need to change the following code:

      lTarget.Interior.ColorIndex = 0

    The ColorIndex value would then just need to be changed to whatever that single colour is.

    Edited once, last by gijsmo ().

  • Good evening gijsmo

    I'd like to know how to make it so that whenever you click on a cell and then leave that cell to go to another, the original colour stays intact and doesn't change colour as in the file I attached in the post: Post 11

    If it's possible, I'd really appreciate it.

    Thank you

    Best regards

  • Apologies to gijsmo but If I may butt in - Just to offer another take on highlighting cells.

    The problem using code to temporarily change the background colour of a cell has already been pointed out above; how to set it back to what was there before when the cursor is moved off the cell.

    Another potential problem is the borders may disappear when the cell colour is reset meaning more code and possibly messing up borders already applied to the cell.

    A different approach using conditional formatting is in the attached workbook. This highlights the selected cell (Only works for single cells - simpler for a demo) and removes the CF when another cell is selected - so existing colours/border formatting is not affected. It will also work with any existing conditional formatting applied to the worksheet.

    I haven't tied to follow/understand the thread and not looked at the attached workbooks, just replying to the comment "... how to make it so that whenever you click on a cell and then leave that cell to go to another, the original colour stays intact".

    There are comments in the code but as this is a shot in the dark, I'll leave it at that unless there are any questions.

  • I have added cytop's example code into the previous workbook - this file is attached below.

    I have made some minor changes in line with how the original workbook was operating.

    Also, I added some code that removes all the temporary conditional formatting in the Workbook_BeforeClose sub. This means the DeleteTempCF sub has been moved to Module 1. So all temporary conditional formatting is removed before the workbook is closed (and re-saved if required).


  • This means the DeleteTempCF sub has been moved to Module 1

    ... which is really where is should be. Everything in the example was just cobbled together.

    If you are removing temporary CFs before closing you can also get rid of anything that refers to worksheet custom properties. That is just there to handle the very first Selection_Change event on the sheet after the workbook is loaded. As the last selected cell is an unknown at that point it is retrieved from Custom Properties to clear any temp CFs saved with the workbook.

  • Thank you, cytop, for your help.

    Thank you, gijsmo, for your help.

    I went to test the file, and the yellow colour doesn't appear when I click on any cell, it's simply blank, and that's not what I want.

    I'm sorry to be bothering you and giving you so much trouble, but I really wanted it so that when I clicked on any cell, it would have a yellow background colour, except, of course, if the cells had Conditional Formatting, then it's not possible to change the colour, because there's already a colour there, I'm talking about cells that are blank and when you click on it, it will have a yellow colour.

    I don't know if I'm making myself clear.

    And once again, I apologise for the inconvenience caused.

    Best regards

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!