estatefinds
08-04-2018, 09:09 AM
1.This code below needs to be reconstructed to work on the active worksheet, not any others.
2. in a range of Q5 to U27 there will be 5 cells at a time that will be colored interior yellow.
3. I need to return the data of interior colored cell yellow to be recorded as for example 1-5-7-12-18 into column W starting at row 5.
4. so everytime I select; as you will see in the example file a combination in column A the data in the range H5 to L27 will colored interior yellow and the data in the Q5 to U27 will be colored interior yellow.
5. the range that i'm focusing on for the code below is the Q5 to U27, so I need the code Below to work with existing event code.
6. so Everytime I select a combination in column A the data in both ranges will be colored interior yellow, then I need the code below to be reconstructed so any cell that gets interior colored yellow, which 5 at the same time in the range Q5 to U27, that data within the cell will be recorded in column W starting at row 5. so it will look like this:
1-5-7-12-18
when you open file select the first combination in column A and youll see the cells being colored interior yellow as described above.
Also when you go back up to the first combination in column A look at the ranges where the cells are interior colored yellow in the range Q5 to U27 and then look over to the right in Column W I had placed the data of the colored interior yellow 1-5-7-12-18 manually so the data that is colored interior yellow as I Select each combination in Column A the data in the range Q5 to U27 will be recoreded in column W starting at row 5.
if any questions let me know. Thank you
any help on this is Appreciated!
Sincerely Dennis
Option ExplicitPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Call Test2(Target)
Application.EnableEvents = True
End Sub
Sub Test2(Target As Range)
Dim R As Range, arr, a
Dim cel As Variant
Set R = Range("Q:U").SpecialCells(2)
For Each cel In R
If cel.Interior.ColorIndex = 6 Then
cel.Interior.ColorIndex = xlNone
End If
Next
Set R = Nothing
Set R = Range("H:L").SpecialCells(2)
For Each cel In R
If cel.Interior.ColorIndex = 6 Then
cel.Interior.ColorIndex = xlNone
End If
Next
'Range("Q:U").Interior.ColorIndex = xlNone
arr = Split(Target, "-")
For Each a In arr
Call DoFind(R, a)
Next
End Sub
Sub DoFind(R, v)
Dim c, firstAddress
Dim Target As Range
With R
Set c = .Find(v, Lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Interior.ColorIndex = xlNone Then c.Interior.ColorIndex = 6
If c.Interior.ColorIndex = 6 Then
If c.Offset(0, 9).Interior.ColorIndex = xlNone Then
c.Offset(0, 9).Interior.ColorIndex = 6
End If
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
THIS CODE ABOVE AND THE CODE BELOW I WOULD TO BE COMBINED, BUT OF COURSE WOULD LIKE THE CODE BELOW TO BE RESTRUCTRED AS DESCRIBED ABOVE. THANK YOU
Sub test()
Dim r As Range, cel As Range
Set r = Range(Sheets(“Sheet2”).Range(Q5:U27),Sheets(“Sheet2”).Range(Q5:U27” & rows.count).End(xlup))
For each cel In r
If cel Interior.Colorindex=6 Then
Range(“Q5:U27”& rows.count). End(xlUp).offset(2).Value =cel.value
End If
Next cel
End Sub
2. in a range of Q5 to U27 there will be 5 cells at a time that will be colored interior yellow.
3. I need to return the data of interior colored cell yellow to be recorded as for example 1-5-7-12-18 into column W starting at row 5.
4. so everytime I select; as you will see in the example file a combination in column A the data in the range H5 to L27 will colored interior yellow and the data in the Q5 to U27 will be colored interior yellow.
5. the range that i'm focusing on for the code below is the Q5 to U27, so I need the code Below to work with existing event code.
6. so Everytime I select a combination in column A the data in both ranges will be colored interior yellow, then I need the code below to be reconstructed so any cell that gets interior colored yellow, which 5 at the same time in the range Q5 to U27, that data within the cell will be recorded in column W starting at row 5. so it will look like this:
1-5-7-12-18
when you open file select the first combination in column A and youll see the cells being colored interior yellow as described above.
Also when you go back up to the first combination in column A look at the ranges where the cells are interior colored yellow in the range Q5 to U27 and then look over to the right in Column W I had placed the data of the colored interior yellow 1-5-7-12-18 manually so the data that is colored interior yellow as I Select each combination in Column A the data in the range Q5 to U27 will be recoreded in column W starting at row 5.
if any questions let me know. Thank you
any help on this is Appreciated!
Sincerely Dennis
Option ExplicitPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Call Test2(Target)
Application.EnableEvents = True
End Sub
Sub Test2(Target As Range)
Dim R As Range, arr, a
Dim cel As Variant
Set R = Range("Q:U").SpecialCells(2)
For Each cel In R
If cel.Interior.ColorIndex = 6 Then
cel.Interior.ColorIndex = xlNone
End If
Next
Set R = Nothing
Set R = Range("H:L").SpecialCells(2)
For Each cel In R
If cel.Interior.ColorIndex = 6 Then
cel.Interior.ColorIndex = xlNone
End If
Next
'Range("Q:U").Interior.ColorIndex = xlNone
arr = Split(Target, "-")
For Each a In arr
Call DoFind(R, a)
Next
End Sub
Sub DoFind(R, v)
Dim c, firstAddress
Dim Target As Range
With R
Set c = .Find(v, Lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Interior.ColorIndex = xlNone Then c.Interior.ColorIndex = 6
If c.Interior.ColorIndex = 6 Then
If c.Offset(0, 9).Interior.ColorIndex = xlNone Then
c.Offset(0, 9).Interior.ColorIndex = 6
End If
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
THIS CODE ABOVE AND THE CODE BELOW I WOULD TO BE COMBINED, BUT OF COURSE WOULD LIKE THE CODE BELOW TO BE RESTRUCTRED AS DESCRIBED ABOVE. THANK YOU
Sub test()
Dim r As Range, cel As Range
Set r = Range(Sheets(“Sheet2”).Range(Q5:U27),Sheets(“Sheet2”).Range(Q5:U27” & rows.count).End(xlup))
For each cel In r
If cel Interior.Colorindex=6 Then
Range(“Q5:U27”& rows.count). End(xlUp).offset(2).Value =cel.value
End If
Next cel
End Sub