• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Design a dynamic table by Macro

amitc4u

Member
I would like the Macro to find the table in the designated sheet , start the text search and search all the cells in the table for cells that contain the text entered by the user.
for each cell that contains the specific text it would paint it in the color that the user entered.
please see the example and detailed instructions in the attached file.

thank's
Amit
 

Attachments

  • Excel Design test.xlsx
    13.7 KB · Views: 9
Hi,

The below code should do the trick

Code:
Sub Loop_Selection()
 
Dim sht, clr, crit
 
sht = Sheets("Design Sheet").Range("E6").Value
clr = Sheets("Design Sheet").Range("E7").Interior.ColorIndex
crit = Sheets("Design Sheet").Range("E5").Text
 
 
Dim cell As Range
 
 
Sheets(sht).Select
 
 
lc = Split(Range("IV3").End(xlToLeft).Address(), "$")(1)
   
lr = Range("B" & Rows.Count).End(xlUp).Row
 
Range("B3:" & lc & lr).Select
 
Dim rng As Range
Set rng = Application.Selection
 
For Each cell In rng
 
If cell.Value Like crit Then
cell.Interior.ColorIndex = clr
End If
 
Next
 
End Sub
 
I tried to copy the code into the file but I got error messagase.
could you please upload a my file with the working code in it?
 
This works great but it paints only the cells with the exact string.
I need it to paint all the cells that contains the string.
 
Back
Top