|
Conditional formatting
Hi, i need a way to use conditional formatting or another way to highlight
cells based on duplicate names group. Ex: 1 john 2 john 3 john 4 mary 5 mary 6 jim 7 cris 8 cris 9 cris I need all cells with john to be highlighted with one colour, the next group (mary) with another color, the next group (jim) with another color....and so on. All names will be ascending. Can this be done? Thanks! |
Conditional formatting
Try the below macro with data in ColA
Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "puiuluipui" wrote: Hi, i need a way to use conditional formatting or another way to highlight cells based on duplicate names group. Ex: 1 john 2 john 3 john 4 mary 5 mary 6 jim 7 cris 8 cris 9 cris I need all cells with john to be highlighted with one colour, the next group (mary) with another color, the next group (jim) with another color....and so on. All names will be ascending. Can this be done? Thanks! |
Conditional formatting
If the previous one gives you syntax error try the below version
Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange End If Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "Jacob Skaria" wrote: Try the below macro with data in ColA Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "puiuluipui" wrote: Hi, i need a way to use conditional formatting or another way to highlight cells based on duplicate names group. Ex: 1 john 2 john 3 john 4 mary 5 mary 6 jim 7 cris 8 cris 9 cris I need all cells with john to be highlighted with one colour, the next group (mary) with another color, the next group (jim) with another color....and so on. All names will be ascending. Can this be done? Thanks! |
Conditional formatting
It's working great!. I have one more question. I need to see how many times
john appear (3), mary (2).....an so on. I need to count the duplicates. How can i do this? Thanks! "Jacob Skaria" a scris: If the previous one gives you syntax error try the below version Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange End If Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "Jacob Skaria" wrote: Try the below macro with data in ColA Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "puiuluipui" wrote: Hi, i need a way to use conditional formatting or another way to highlight cells based on duplicate names group. Ex: 1 john 2 john 3 john 4 mary 5 mary 6 jim 7 cris 8 cris 9 cris I need all cells with john to be highlighted with one colour, the next group (mary) with another color, the next group (jim) with another color....and so on. All names will be ascending. Can this be done? Thanks! |
Conditional formatting
Try the below version which will put in the count in ColB for every last
unique entry Sub Macro1() Dim lngRow As Long, blnChange As Boolean, intCounter As Integer For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row intCounter = intCounter + 1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("B" & lngRow - 1) = intCounter: intCounter = 0 End If Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next Range("B" & lngRow - 1) = intCounter + 1 End Sub -- Jacob "puiuluipui" wrote: It's working great!. I have one more question. I need to see how many times john appear (3), mary (2).....an so on. I need to count the duplicates. How can i do this? Thanks! "Jacob Skaria" a scris: If the previous one gives you syntax error try the below version Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange End If Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "Jacob Skaria" wrote: Try the below macro with data in ColA Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "puiuluipui" wrote: Hi, i need a way to use conditional formatting or another way to highlight cells based on duplicate names group. Ex: 1 john 2 john 3 john 4 mary 5 mary 6 jim 7 cris 8 cris 9 cris I need all cells with john to be highlighted with one colour, the next group (mary) with another color, the next group (jim) with another color....and so on. All names will be ascending. Can this be done? Thanks! |
Conditional formatting
It's working, but i need the macro to put the count in ColB for every first
unique entry, because i need to run advance filter and to remain with unique entries. The advance filter keeps only the first record, so i need this macro to put the count in ColB for every first unique entry, and then to run advance filter. Can this be done? Thanks! "Jacob Skaria" a scris: Try the below version which will put in the count in ColB for every last unique entry Sub Macro1() Dim lngRow As Long, blnChange As Boolean, intCounter As Integer For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row intCounter = intCounter + 1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("B" & lngRow - 1) = intCounter: intCounter = 0 End If Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next Range("B" & lngRow - 1) = intCounter + 1 End Sub -- Jacob "puiuluipui" wrote: It's working great!. I have one more question. I need to see how many times john appear (3), mary (2).....an so on. I need to count the duplicates. How can i do this? Thanks! "Jacob Skaria" a scris: If the previous one gives you syntax error try the below version Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange End If Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "Jacob Skaria" wrote: Try the below macro with data in ColA Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "puiuluipui" wrote: Hi, i need a way to use conditional formatting or another way to highlight cells based on duplicate names group. Ex: 1 john 2 john 3 john 4 mary 5 mary 6 jim 7 cris 8 cris 9 cris I need all cells with john to be highlighted with one colour, the next group (mary) with another color, the next group (jim) with another color....and so on. All names will be ascending. Can this be done? Thanks! |
Conditional formatting
Try
Sub Macro1() Dim lngRow As Long, blnChange As Boolean, intCount As Integer, lngFRow As Long lngFRow = 1 For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row intCounter = intCounter + 1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("B" & lngFRow) = intCount: intCount = 0 lngFRow = lngRow End If Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next Range("B" & lngFRow) = intCount + 1 End Sub -- Jacob "puiuluipui" wrote: It's working, but i need the macro to put the count in ColB for every first unique entry, because i need to run advance filter and to remain with unique entries. The advance filter keeps only the first record, so i need this macro to put the count in ColB for every first unique entry, and then to run advance filter. Can this be done? Thanks! "Jacob Skaria" a scris: Try the below version which will put in the count in ColB for every last unique entry Sub Macro1() Dim lngRow As Long, blnChange As Boolean, intCounter As Integer For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row intCounter = intCounter + 1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("B" & lngRow - 1) = intCounter: intCounter = 0 End If Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next Range("B" & lngRow - 1) = intCounter + 1 End Sub -- Jacob "puiuluipui" wrote: It's working great!. I have one more question. I need to see how many times john appear (3), mary (2).....an so on. I need to count the duplicates. How can i do this? Thanks! "Jacob Skaria" a scris: If the previous one gives you syntax error try the below version Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange End If Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "Jacob Skaria" wrote: Try the below macro with data in ColA Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "puiuluipui" wrote: Hi, i need a way to use conditional formatting or another way to highlight cells based on duplicate names group. Ex: 1 john 2 john 3 john 4 mary 5 mary 6 jim 7 cris 8 cris 9 cris I need all cells with john to be highlighted with one colour, the next group (mary) with another color, the next group (jim) with another color....and so on. All names will be ascending. Can this be done? Thanks! |
Conditional formatting
Hi, the code put the result in the first cell, but the result is 0.
Thanks! "Jacob Skaria" a scris: Try Sub Macro1() Dim lngRow As Long, blnChange As Boolean, intCount As Integer, lngFRow As Long lngFRow = 1 For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row intCounter = intCounter + 1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("B" & lngFRow) = intCount: intCount = 0 lngFRow = lngRow End If Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next Range("B" & lngFRow) = intCount + 1 End Sub -- Jacob "puiuluipui" wrote: It's working, but i need the macro to put the count in ColB for every first unique entry, because i need to run advance filter and to remain with unique entries. The advance filter keeps only the first record, so i need this macro to put the count in ColB for every first unique entry, and then to run advance filter. Can this be done? Thanks! "Jacob Skaria" a scris: Try the below version which will put in the count in ColB for every last unique entry Sub Macro1() Dim lngRow As Long, blnChange As Boolean, intCounter As Integer For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row intCounter = intCounter + 1 If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("B" & lngRow - 1) = intCounter: intCounter = 0 End If Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next Range("B" & lngRow - 1) = intCounter + 1 End Sub -- Jacob "puiuluipui" wrote: It's working great!. I have one more question. I need to see how many times john appear (3), mary (2).....an so on. I need to count the duplicates. How can i do this? Thanks! "Jacob Skaria" a scris: If the previous one gives you syntax error try the below version Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange End If Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "Jacob Skaria" wrote: Try the below macro with data in ColA Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < Range("A" & lngRow - 1) Then blnChange = Not blnChange Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15) Next End Sub -- Jacob "puiuluipui" wrote: Hi, i need a way to use conditional formatting or another way to highlight cells based on duplicate names group. Ex: 1 john 2 john 3 john 4 mary 5 mary 6 jim 7 cris 8 cris 9 cris I need all cells with john to be highlighted with one colour, the next group (mary) with another color, the next group (jim) with another color....and so on. All names will be ascending. Can this be done? Thanks! |
| All times are GMT +1. The time now is 05:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com