Page 1 of 1
Select yellow
Posted: Mon Oct 12, 2020 9:29 pm
by sna
Hi there!
I would need your help regarding code to select only rows have yellow color.
I try to code but not working
Also attached template
Best wishes
Re: Select yellow
Posted: Mon Oct 12, 2020 10:55 pm
by puriwutpokin
Try it
Code: Select all
Sub DeleteYello()
Dim rg As Range, i As Long
Set rg = Range(Range("a2"), Range("A" & Rows.Count).End(xlUp))
For i = rg.Rows.Count To 1 Step -1
If rg(i).Interior.ColorIndex = 6 Then
rg(i).EntireRow.Delete Shift:=xlUp
End If
Next i
End Sub
Re: Select yellow
Posted: Tue Oct 13, 2020 2:57 am
by sna
Thanks .I need select only yellow rows no delete
Re: Select yellow
Posted: Tue Oct 13, 2020 8:33 am
by snasui
The example code as below:
Code: Select all
'Other code
Dim strRg As String
Set rg = Range(Range("a2"), Range("A" & Rows.Count).End(xlUp))
For i = 2 To rg.Rows.Count
If rg(i).Interior.ColorIndex = 6 Then
strRg = strRg & "," & rg(i).Address(0, 0)
End If
Next
Range(Mid(strRg, 2)).EntireRow.Select
'Other code
Re: Select yellow
Posted: Tue Oct 13, 2020 9:22 am
by sna
Thank you will update soon
Re: Select yellow
Posted: Tue Oct 13, 2020 10:11 am
by sna
It occurs run-time-error '1004'
It highlight this part
Range(Mid(strRg, 2)).EntireRow.Select
Re: Select yellow
Posted: Tue Oct 13, 2020 10:28 am
by sna
Here's an update
Re: Select yellow
Posted: Tue Oct 13, 2020 4:42 pm
by snasui
The error occurred because not met condition. Adjust your code with below:
Code: Select all
'Other code
Dim strRg As String
Set rg = Sheets("Bakong").Range(Range("a2"), Range("a" & Rows.Count).End(xlUp))
For i = rg.Rows.Count To 1 Step -1
If rg(i).Interior.ColorIndex = 6 Then
strRg = strRg & "," & rg(i).Address(0, 0)
End If
Next i
If strRg <> "" Then
Range(Mid(strRg, 2)).EntireRow.Select
End If
'Other code
Re: Select yellow
Posted: Tue Oct 13, 2020 7:44 pm
by sna
Hi I test it but it still work nothing
Re: Select yellow
Posted: Tue Oct 13, 2020 7:59 pm
by snasui
Please read my post #8 with carefully.
Re: Select yellow
Posted: Tue Oct 13, 2020 8:35 pm
by sna
I add this line if strRg <> "" Then
To check strRg value like in post #8 but still no luck
Re: Select yellow
Posted: Tue Oct 13, 2020 8:42 pm
by snasui
snasui wrote: Tue Oct 13, 2020 4:42 pm
The error occurred because not met condition.
The above is important information
Re: Select yellow
Posted: Tue Oct 13, 2020 8:51 pm
by sna
Hi what is the condition behind?
I just need code to select row with yellow color
from column A to I only.
Re: Select yellow
Posted: Tue Oct 13, 2020 8:58 pm
by snasui
Please check this statement.
rg(i).Interior.ColorIndex = 6
Re: Select yellow
Posted: Tue Oct 13, 2020 9:41 pm
by sna
I check the code work but yellow here comes from conditional formatting.this file came from my friend so the code won't trigger.how can we handle this?I just realized this because he doesn't say color it is from conditional formatting
Re: Select yellow
Posted: Tue Oct 13, 2020 10:17 pm
by snasui
Adjust with the code below:
Code: Select all
'Other code
For i = rg.Rows.Count To 1 Step -1
If rg(i).DisplayFormat.Interior.ColorIndex = 6 Then
strRg = strRg & "," & rg(i).Address(0, 0)
End If
Next i
'Other code
Re: Select yellow
Posted: Tue Oct 13, 2020 10:58 pm
by sna
Thanks
Re: Select yellow
Posted: Tue Oct 13, 2020 11:15 pm
by sna
It is error this line
run time error '438'
if rg(i).DisplayFormat.Interior.ColorIndex = 6
Re: Select yellow
Posted: Wed Oct 14, 2020 7:28 am
by snasui
It not show this problem in my machine.
