VBA Macro to select and bring back a new campaign code

[digital786]

Hi Everyone Hope your all well.

Hopefully someone can help me with this, am trying to use VBA macro to help me generate a unique campaign code used in emails but i been stuck with it for a while now.

I have 2 sheets.

• Sheet 1 (Campaign Codes) is where the unique ID code is going to go in cell 'D4'
• Sheet 2 (Config) is where a list of all the campaign codes are held. column 'H2' down to 'H3000'
  
  • the campaign codes would be listed in numerical order for example campaign0001, campaign 0002, campaign0003

I have a button set in sheet 1 which i will assign the macro to.


Working example:

1. I press the macro button
2. Macro runs and brings back ONLY 1 unique E-code EACH TIME from the list in sheet 2, which has not been used before and inserts it into Sheet 1 (Campaign Codes) cell 'D4'
3. In the logic also to highlight the cell in sheet 2 of the unique E-Code which has been used in red and the rest remain in green. So when it comes to clicking the macro button it looks for cell in red and green back campaign code for the cell below in green.

I would be extremely great full if someone could help me with this. I've attached the document i am working on.

Email Tracking Document v3-Macro Enabled.xlsm

[SamT]

Something like this:

Sub Button_Click()
Dim Cel As Range

Application.FindFormat.Interior.ColorIndex = 4

Set Cel = Sheets("Config").Range("H:H").Find(What:="*", _
   After:=Range("H1"), SearchFormat:=True)

If Cel Is Nothing Then
  MsgBox "No unused Campaign Codes available."
  'Exit Sub
End If

Cel.Interior.ColorIndex = 3
Sheets("Campaign Codes").Range("D4") = Cel

End Sub

[NoSparks]

maybe this:

Sub Rectangle1_Click()
Dim cel As Range
For Each cel In Sheets("Config").Range("H2:H3000")
    If cel.Interior.ColorIndex <> 3 Then
        Sheets("Campaign Codes").Range("D4").Value = cel.Value
        cel.Interior.ColorIndex = 3
        Exit For
    End If
Next cel
End Sub

[SamT]

Using For Each on 3000 Cells takes 1000 times as long as on 3 cells

Using Find on 3000 cells take the same time as on 3 cells.

[NoSparks]

I agree but at least it works.

Isn't color index 4 = RGB(0,255,0) ?

OP's cells are RGB(198,224,180)

[digital786]

Hi Guys

SamT thank you very much for reply back to my query i tried you VBA code but unfortunately it didn't work. But thank you NoSparks your VBA code was perfect its working like a charm

I can happily say this has been resolved.