hello
I'm new to vba. Can someone help with this?
I appreciate it
the Range of cells with data will always be different
Dynamically highlight all cells that equal the active cell value
-
-
-
Re: Using the rng of cells with data color all cells with val equal to clicked cell v
Welcome to Ozgrid.
How do you expect anyone to help with such a sparse explanation of your requirements?
Explain more clearly and in greater detail.
Better yet, if you upload a sample workbook (exact structure, dummy data) to provide an accurate context and some content to work with you'll get a precise solution more quickly. The workbook only needs to contain enough data to illustrate the need. It should contain a clear, explicit explanation of your requirements, criteria, etc. and show examples of the desired results.If you get an error when uploading due to size limitation, use ZIP compression or a file share and provide the link.
-
Re: Using the rng of cells with data color all cells with val equal to clicked cell v
Sorry, I was trying to post in the middle of a busy day.
I will copy and paste data from a website into an excel workbook. The data will have a set number of columns but the rows will vary every time. I then want to be able to click any cell and have all values equal to the one on the clicked cell
filled with color. When I click a different cell, I want the cells which were colored on the first click to go back to normal (white)
and the new clicked cell and matching cells to be colored etc.Thank you
-
Re: Dynamically highlight all cells that equal the active cell value
Copy this to a regular. It works as you asked, briefly tested with various row and column ranges
Code
Display MoreOption Explicit Sub Color_Matching_Cells() Dim lastrow As Long, lastcol As Long, i As Long Application.ScreenUpdating = False With ActiveSheet .AutoFilterMode = False lastcol = .Cells.Find(what:="*", after:=.Cells(1, 1), searchorder:=xlByColumns, searchdirection:=xlPrevious).Column lastrow = .Cells.Find(what:="*", after:=.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row .Range(.Cells(1, 1), .Cells(lastrow, lastcol)).Interior.Color = xlNone On Error Resume Next For i = 1 To lastcol .Range(.Cells(1, i), .Cells(lastrow, i)).AutoFilter field:=1, Criteria1:=ActiveCell.Value .Range(.Cells(1, i), .Cells(lastrow - 1, i)).Offset(1, 0).SpecialCells(12).Interior.Color = vbYellow .AutoFilterMode = False Next i End With Application.ScreenUpdating = True End Sub
-
Re: Dynamically highlight all cells that equal the active cell value
Hello
Thx
I will try this as soon as I get a chance
Should I drop this code into sheet1/worsheet/selectionchange event? -
-
Re: Dynamically highlight all cells that equal the active cell value
Either place the entire procedure in a module and call from the selection change event or copy just the body of the code, ignoring the Sub/End Sub lines, and paste into the Worksheet_Selection change event.
-
Re: Dynamically highlight all cells that equal the active cell value
To put it in a regular module
Alt + F11 to open VB Editor
Menu > Insert > Module
Copy and paste the code into the module.To run the macro activate the sheet then press Alt + F8 to open the Macro Dialog.
-
Re: Dynamically highlight all cells that equal the active cell value
Hello
I have that code working also. I modified it to keep coloring new clicked cells also.
The only other thing I would like to do is
How do I count the colored cells in each row and then write the number of colored cells per row at any available cell after the last column (all rows will always have same number of columns)
Thanks for any inputSo for example, If I have 10 rows of data. and if I clicked on 3 different values. Right now, I have the code coloring all those values in the range of data. Any Row can have 0,1,2,3 colored cells. i want whatever the number of colored cells is in each row ,written after the last column of every row
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!