Joining two VBA Range Excel 2007 codes

Bikke · Aug 26th 2023

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

Regards

Code

Private Sub Worksheet_Change(ByVal Target As Range)

Dim shp As Shape, rng As Range
  If Target.Count > 1 Then Exit Sub
   If Intersect(Target, [D8:D227]) Is Nothing Or Target.Value <> "" Then Exit Sub
   Cells(Target.Row, 5).Resize(, 11) = "": Cells(Target.Row, 17).Resize(, 3) = ""
    
End Sub


Dim lTarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not lTarget Is Nothing Then
        lTarget.Interior.ColorIndex = 0
    End If

    Target.Interior.ColorIndex = 19

    Set lTarget = Target
End Sub

Display More

rollis13 · Aug 27th 2023

Move the line:

Dim lTarget As Range

before the line (on top):

Private Sub Worksheet_Change(ByVal Target As Range)

Bikke · Aug 27th 2023

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.

Code

Private Sub Worksheet_Change(ByVal Target As Range)

Dim shp As Shape, rng As Range
  If Target.Count > 1 Then Exit Sub
   If Intersect(Target, [D8:D227]) Is Nothing Or Target.Value <> "" Then Exit Sub
   Cells(Target.Row, 5).Resize(, 11) = "": Cells(Target.Row, 17).Resize(, 3) = ""
    
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lTarget As Range

    If Not lTarget Is Nothing Then
        lTarget.Interior.ColorIndex = 0
    End If

    Target.Interior.ColorIndex = 19

    Set lTarget = Target
End Sub

Display More

Thank you

Bikke · Aug 27th 2023

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

Mumps · Aug 27th 2023

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.

Bikke · Aug 27th 2023

I didn't think it would be necessary, which is why I didn't put the file up.

Thank you for your attention.

Best regards

P.S.- The second code is so that whenever I click on any cell, a colour appears, in this case yellow, and when I click on another cell, that cell becomes white and the other yellow, and so on.

rollis13 · Aug 27th 2023

Quote from rollis13

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.

Bikke · Aug 27th 2023

I've done it and it still gives me an error.

gijsmo · Aug 27th 2023

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:

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Unprotect ""

    If Not lTarget Is Nothing Then
        lTarget.Interior.ColorIndex = 0
    End If

    Target.Interior.ColorIndex = 19

    Set lTarget = Target
    
    Protect ""
End Sub

Display More

Bikke · Aug 27th 2023

Good evening gijsmo

Perfect, that's it, it works perfectly.

Thank you very much for your help.

Have a good working week.

Best Regards

Bikke · Aug 29th 2023

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

Regards

gijsmo · Aug 30th 2023

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.

Bikke · Aug 30th 2023

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

gijsmo · Aug 31st 2023

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

Code

  lTarget.Interior.ColorIndex = 0

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

Bikke · Aug 31st 2023

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

**cytop** · Sep 1st 2023

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.

gijsmo · Sep 1st 2023

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).

Existencia.Geral_V1_2023_V2.xls

**cytop** · Sep 1st 2023

Quote from gijsmo

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.

Bikke · Sep 1st 2023

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

gijsmo · Sep 2nd 2023

I'm not sure what you're seeing Bikke, I specifically coded it to have the yellow background colour that you had in your original example:

Joining two VBA Range Excel 2007 codes

Participate now!

Share

Similar Threads

How to find and match the ID number from one workbook to another workbook - excel vba

Tags