every 10th selection of same item from combo box to be highlighted.

[KingGoku]

Hi there,

I need help writing a macro that will highlight every 10th selection of same item from userform combo box. is that possible?

Private Sub ComboBoxProduct_change()

Dim m As String

'Convert date into Julian days
Dim awb As WorksheetFunction, JulianDt As String
Dim ws As Worksheet
Dim DateCell, ProdCell As Range
Dim DayCount, RowCell As Long
Set awb = Application.WorksheetFunction

JulianDt = awb.Text(awb.Days(Date, 1) - awb.Days(DateSerial(Year(Date), 1, 0), 1), "000")

m = FormSelectSht.ComboBoxProduct.Value

For Each ws In ThisWorkbook.Worksheets
    If ws.Name = ComboBoxMachines.Value Then Exit For
Next ws

DayCount = 1

For RowCell = 5 To 200
    If ws.Cells(RowCell, 1).Value = Date And ws.Cells(RowCell, 2) = ComboBoxProduct.Value Then DayCount = DayCount + 1
Next RowCell


FormSelectSht.TextBoxLot.Value = JulianDt + "-" + Format(Date, "yyyy") + "-" + m + "-" + Format(DayCount, "000")


End Sub

 

[Muhammad_Usman]

Check out this modify code



Private Sub ComboBoxProduct_Change()

Dim m As String
Dim awb As WorksheetFunction
Dim JulianDt As String
Dim ws As Worksheet
Dim DateCell, ProdCell As Range
Dim DayCount, RowCell As Long
Dim SelectionCount As Long

Set awb = Application.WorksheetFunction

JulianDt = awb.Text(awb.Days(Date, 1) - awb.Days(DateSerial(Year(Date), 1, 0), 1), "000")

m = FormSelectSht.ComboBoxProduct.Value

For Each ws In ThisWorkbook.Worksheets
If ws.Name = ComboBoxMachines.Value Then Exit For
Next ws

DayCount = 1
SelectionCount = 1

For RowCell = 5 To 200
If ws.Cells(RowCell, 1).Value = Date And ws.Cells(RowCell, 2) = ComboBoxProduct.Value Then
DayCount = DayCount + 1
' Check if it's the 10th selection of the same item
If SelectionCount Mod 10 = 0 Then
ws.Cells(RowCell, 1).Interior.Color = RGB(255, 255, 0) ' Highlight in yellow
End If
SelectionCount = SelectionCount + 1
End If
Next RowCell

FormSelectSht.TextBoxLot.Value = JulianDt & "-" & Format(Date, "yyyy") & "-" & m & "-" & Format(DayCount, "000")

End Sub

 

[KingGoku]

Check out this modify code



Private Sub ComboBoxProduct_Change()

Dim m As String
Dim awb As WorksheetFunction
Dim JulianDt As String
Dim ws As Worksheet
Dim DateCell, ProdCell As Range
Dim DayCount, RowCell As Long
Dim SelectionCount As Long

Set awb = Application.WorksheetFunction

JulianDt = awb.Text(awb.Days(Date, 1) - awb.Days(DateSerial(Year(Date), 1, 0), 1), "000")

m = FormSelectSht.ComboBoxProduct.Value

For Each ws In ThisWorkbook.Worksheets
If ws.Name = ComboBoxMachines.Value Then Exit For
Next ws

DayCount = 1
SelectionCount = 1

For RowCell = 5 To 200
If ws.Cells(RowCell, 1).Value = Date And ws.Cells(RowCell, 2) = ComboBoxProduct.Value Then
DayCount = DayCount + 1
' Check if it's the 10th selection of the same item
If SelectionCount Mod 10 = 0 Then
ws.Cells(RowCell, 1).Interior.Color = RGB(255, 255, 0) ' Highlight in yellow
End If
SelectionCount = SelectionCount + 1
End If
Next RowCell

FormSelectSht.TextBoxLot.Value = JulianDt & "-" & Format(Date, "yyyy") & "-" & m & "-" & Format(DayCount, "000")

End Sub

hi muhammad,

thanks so much it works.

 

[KingGoku]

Check out this modify code



Private Sub ComboBoxProduct_Change()

Dim m As String
Dim awb As WorksheetFunction
Dim JulianDt As String
Dim ws As Worksheet
Dim DateCell, ProdCell As Range
Dim DayCount, RowCell As Long
Dim SelectionCount As Long

Set awb = Application.WorksheetFunction

JulianDt = awb.Text(awb.Days(Date, 1) - awb.Days(DateSerial(Year(Date), 1, 0), 1), "000")

m = FormSelectSht.ComboBoxProduct.Value

For Each ws In ThisWorkbook.Worksheets
If ws.Name = ComboBoxMachines.Value Then Exit For
Next ws

DayCount = 1
SelectionCount = 1

For RowCell = 5 To 200
If ws.Cells(RowCell, 1).Value = Date And ws.Cells(RowCell, 2) = ComboBoxProduct.Value Then
DayCount = DayCount + 1
' Check if it's the 10th selection of the same item
If SelectionCount Mod 10 = 0 Then
ws.Cells(RowCell, 1).Interior.Color = RGB(255, 255, 0) ' Highlight in yellow
End If
SelectionCount = SelectionCount + 1
End If
Next RowCell

FormSelectSht.TextBoxLot.Value = JulianDt & "-" & Format(Date, "yyyy") & "-" & m & "-" & Format(DayCount, "000")

End Sub

how can i get the code to look at only first first 4 string code "0000")

 

[Muhammad_Usman]

hi muhammad,

thanks so much it works.

Mark it as Solved Thanks

 

[Muhammad_Usman]

how can i get the code to look at only first first 4 string code "0000")

Private Sub ComboBoxProduct_Change()

    Dim m As String
    Dim awb As WorksheetFunction
    Dim JulianDt As String
    Dim ws As Worksheet
    Dim DateCell, ProdCell As Range
    Dim DayCount, RowCell As Long
    Dim SelectionCount As Long
   
    Set awb = Application.WorksheetFunction
   
    JulianDt = awb.Text(awb.Days(Date, 1) - awb.Days(DateSerial(Year(Date), 1, 0), 1), "000")
   
    m = FormSelectSht.ComboBoxProduct.Value
   
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = ComboBoxMachines.Value Then Exit For
    Next ws
   
    DayCount = 1
    SelectionCount = 1
   
    For RowCell = 5 To 200
        If ws.Cells(RowCell, 1).Value = Date And ws.Cells(RowCell, 2) = ComboBoxProduct.Value Then
            DayCount = DayCount + 1
            ' Check if it's the 10th selection of the same item
            If SelectionCount Mod 10 = 0 Then
                ' Check if the first 4 characters of TextBoxLot.Value are "0000"
                If Left(FormSelectSht.TextBoxLot.Value, 4) = "0000" Then
                    ' You can add your code here to change the cell color if needed.
                End If
            End If
            SelectionCount = SelectionCount + 1
        End If
    Next RowCell
   
    FormSelectSht.TextBoxLot.Value = JulianDt & "-" & Format(Date, "yyyy") & "-" & m & "-" & Format(DayCount, "000")

End Sub

 

[Fluff]

@Muhammad_Usman in future when posting code please use code tags. How to Post Your VBA Code

 

[Muhammad_Usman]

@Muhammad_Usman in future when posting code please use code tags. How to Post Your VBA Code

ok

 

[KingGoku]

Private Sub ComboBoxProduct_Change()

    Dim m As String
    Dim awb As WorksheetFunction
    Dim JulianDt As String
    Dim ws As Worksheet
    Dim DateCell, ProdCell As Range
    Dim DayCount, RowCell As Long
    Dim SelectionCount As Long
  
    Set awb = Application.WorksheetFunction
  
    JulianDt = awb.Text(awb.Days(Date, 1) - awb.Days(DateSerial(Year(Date), 1, 0), 1), "000")
  
    m = FormSelectSht.ComboBoxProduct.Value
  
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = ComboBoxMachines.Value Then Exit For
    Next ws
  
    DayCount = 1
    SelectionCount = 1
  
    For RowCell = 5 To 200
        If ws.Cells(RowCell, 1).Value = Date And ws.Cells(RowCell, 2) = ComboBoxProduct.Value Then
            DayCount = DayCount + 1
            ' Check if it's the 10th selection of the same item
            If SelectionCount Mod 10 = 0 Then
                ' Check if the first 4 characters of TextBoxLot.Value are "0000"
                If Left(FormSelectSht.TextBoxLot.Value, 4) = "0000" Then
                    ' You can add your code here to change the cell color if needed.
                End If
            End If
            SelectionCount = SelectionCount + 1
        End If
    Next RowCell
  
    FormSelectSht.TextBoxLot.Value = JulianDt & "-" & Format(Date, "yyyy") & "-" & m & "-" & Format(DayCount, "000")

End Sub

Hi Muhammad,
it didn't work. so what i want is if you have look at picture. i want code to look at first 4 words or number in product column and if it has appeared 10th time. i want row to highlight. the first 4 digit could vary. sometime it's a word sometime it's numbers.