Posts by Justin Doward

    Hello CapG

    Not sure why you would not just do this by seperating the text into two columns and using the vlookup function native to excel, but this code should work for the text as provided:

    Hi Jason,

    I cannot see how the sheets relate, ID 100000 for example disappears in sheet 3, ID 1040000 repeats on sheet 3 except it has brackets around the negative value and no account is taken of the 1459 that is present on sheet 2. ID 2309D is only on sheet 2 and nowhere else.

    Essentially I do not see what is supposed to be occurring.

    Hello UA,

    Try the attached, the code is as below:

    Note that I have made the list an excel table, it is not really necessary just how I have been doing things lately. Just use insert table and highlight your table of interest.

    There is an error thrown that I have not worked out, but the resume next corrects it for the list provided, it may be quite slow on your large database so try it on a few hundred/thousand lines first and see how it goes.

    Copy of SampleDataMediaList.xlsm


    Hi Splat,

    Roy's point is that there is no scenario in which coding to select the cell will be more efficient then just writing to or taking information from the cell directly. Selecting ranges tends to be used by early coders before they learn how to navigate things properly, I believe Roy is trying to determine the overall intent of your coding so he can point you in the right direction but if you are happy that the system does what you are after then stick with it for now, you will probably correct it in the future.

    NB: you can easily load a full excel file here and get a working example returned to you where appropriate, i generally would not download and open a zip file.

    Glad my earlier post helped!



    Hi MA,

    The code you have provided does not really do anything like what you are requesting, is there a reason you are using a combination of system popup and msgbox rather than just using msgbox?

    Do you want a message to be displayed when a cell changes or do you want a macro to run through the ranges and popup/msgbox on all values?

    Hi Mamun,

    You can try the approach in the attachment: Book1.xlsm

    I am not sure if you know how to navigate the code?

    there is code on the worksheet:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("G9:G1200")) Is Nothing Then
    If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then GoTo Exit_Sub
    End If
    End Sub

    Modify the range G9:G1200 as required.

    the this code attached to the userform:

    Just create your list of terms, insert it as a table (with header) and name it then change ws = sheet1 to refer to whichever sheet your table is one, and the Table1 to be whatever you name your table and it should work in whatever context you need.

    Let me know how it goes.


    You can replace the code in the workbook_SheetChange event with:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
    If Not Intersect(Sh.Range(target.Address), Sh.Range("C7:U41")) Is Nothing Then
    Application.ScreenUpdating = False
    Call background_color_count
    Application.ScreenUpdating = True
    End If
    End Sub

    Hi Hampton,

    I got this to work:

    Place a workbook_SheetChange event in the workbook code sheet.

    Then the sub in a module.

    I am sure there must be a more efficient way to check if iNts is empty but I kept getting a global method fail.

    Let me know how it goes.


    The function would be a better solution, rather than having to run the macro every time. Either that or writing the code into the sheetchange event so it updates whenever the conditional format is activated. But if the macro does what you need all is good :)

    Hi Roy, Hampton,

    I did not test the function but I expect it will not count cells that have been formatted using conditional format, to modify the function to count these cells insert DisplayFormat whenever referencing the colorindex, eg:


    rCl.Interior.ColorIndex = lCol


    rCl.DisplayFormat.Interior.ColorIndex = lCol

    Without the displayformat the code only counts cells that have had the background color changed using fill-color.




    Try this code in the sheet you posted.

    Let me know if it does what you are after.


    Try this code in the code for the sheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B3:BJ67")) Is Nothing Then
    If Range("BR" & Target.Row) > 116 Then
    MsgBox "over"
    ActiveSheet.Unprotect Password:="PW"
    Range("B" & Target.Row & ":BJ" & Target.Row).Locked = True
    ActiveSheet.Protect Password:="PW"
    End If
    End If
    End Sub

    let me know if it does what you are after.

    Hi DJ,

    Your scenario would be a lot easier to "imagine" if you provided an example sheet. In what you have written you are suggesting a column full of names, in column A but then you want to hide and show column A. What is happening to the names here? Do you just want a list of names, each name can have any number of lines inserted between one name and the next depending on a selected team size?

    Whatever the scenario an example sheet will save a lot of time.

    Hi Mikey,

    This will work on the example you have posted, itassumes the same format is always true.

    Function GetName3(target As String)
    x = InStr(1, target, Chr(10))
    If Left(target, 4) = "Dear" Then
    GetName3 = Trim(Mid(target, 5, x - 5))
    GetName3 = Trim(Mid(target, 1, x - 1))
    End If
    End Function

    HI KD

    I think this is what you were trying to do.

    Excel Template Macro V10 JDEDIT.xlsm

    Let me know how it goes.

    I was not sure why you were creating the sheets, then naming them using the array, and then renaming them in the same workbook open event, so I deleted that step... just change the array to name them correctly the first time.

    I also moved the array to the sheet so it might be easier to change the sheetnames etc, but you can run it from your original array just fine if you delete the list on the sheet.

    There were a few other confusions in there, but I think most of what you are after is there.