wrong colour index value

E

EDUCATED MONKEY

Board Regular
Joined
Jul 17, 2011
Messages
218
I wrote the code below to retreive a lable for a list and than mark the label as use, I am sure there are much better way to do this, what I chose to do was inspect the lables colour index property move that label to the active cell in the work I was using at the time then change the index colour so next pass the label next in line was selected.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
Having had a success with this I attempted to modify it to find words in a column that had been coditionaly formated, red in this case, and delete the entire row upon which the word was found
<o:p> </o:p>
Unfortunatly I seem to have lost the modifyed code any way the problem was that it work in test but not in practice, as their was clearly some diffrence between conditionaly formated cells and those formated using the fill colour icon on the task bar at the bottom of the excel window.
The only other piece of information that I have is the no matter what the conditionally formated cell was the return colour index value was that of the column, if the particular column was green the value returned was 4 and yellow 6 even thou the cell in question was red
<o:p> </o:p>
<o:p> </o:p>
Sub RetrieveLabe()<o:p></o:p>
'<o:p></o:p>
' RetrieveLabe Macro<o:p></o:p>
' <o:p></o:p>
'<o:p></o:p>
' Keyboard Shortcut: Ctrl+Shift+L<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
Worksheets("GROUP NOTICE").Activate<o:p></o:p>
Q = Worksheets("GROUP NOTICE").Range("C1").Value<o:p></o:p>
For i = 1 To Q<o:p></o:p>
Worksheets("GROUP NOTICE").Cells(i, 1).Activate<o:p></o:p>
x = ActiveCell.Interior.ColorIndex<o:p></o:p>
If x = 6 Then<o:p></o:p>
i = Q<o:p></o:p>
y = ActiveCell.Address<o:p></o:p>
End If<o:p></o:p>
Next i<o:p></o:p>
Worksheets("GROUP NOTICE").Activate<o:p></o:p>
ActiveCell.Select<o:p></o:p>
ActiveCell.Interior.ColorIndex = 45<o:p></o:p>
Selection.copy<o:p></o:p>
Worksheets("NEW SHEET").Activate<o:p></o:p>
ActiveSheet.Paste<o:p></o:p>
Worksheets("GROUP NOTICE").Activate<o:p></o:p>
ActiveSheet.Paste<o:p></o:p>
Worksheets("NEW SHEET").Activate<o:p></o:p>
Application.ScreenUpdating = True<o:p></o:p>
<o:p> </o:p>
End Sub<o:p></o:p>
<o:p> </o:p>


<o:p> </o:p>
<TABLE style="MARGIN: auto 6.75pt; WIDTH: 87.25pt; BORDER-COLLAPSE: collapse; mso-table-overlap: never; mso-table-lspace: 9.0pt; mso-table-rspace: 9.0pt; mso-table-anchor-vertical: paragraph; mso-table-anchor-horizontal: column; mso-table-left: left; mso-table-top: .05pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=116 align=left><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 87.25pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=116 noWrap>CLASSICS<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 87.25pt; PADDING-RIGHT: 5.4pt; BACKGROUND: red; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=116 noWrap><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t88 filled="f" path="m,qx10800@0l10800@2qy21600@11,10800@3l10800@1qy,21600e" adj="1800,10800" o:spt="88" coordsize="21600,21600"><v:formulas><v:f eqn="val #0"></v:f><v:f eqn="sum 21600 0 #0"></v:f><v:f eqn="sum #1 0 #0"></v:f><v:f eqn="sum #1 #0 0"></v:f><v:f eqn="prod #0 9598 32768"></v:f><v:f eqn="sum 21600 0 @4"></v:f><v:f eqn="sum 21600 0 #1"></v:f><v:f eqn="min #1 @6"></v:f><v:f eqn="prod @7 1 2"></v:f><v:f eqn="prod #0 2 1"></v:f><v:f eqn="sum 21600 0 @9"></v:f><v:f eqn="val #1"></v:f></v:formulas><v:path textboxrect="0,@4,7637,@5" o:connectlocs="0,0;21600,@11;0,21600" o:connecttype="custom" arrowok="t"></v:path><v:handles><v:h yrange="0,@8" position="center,#0"></v:h><v:h yrange="@9,@10" position="bottomRight,#1"></v:h></v:handles></v:shapetype><v:shape style="Z-INDEX: 1; POSITION: absolute; MARGIN-TOP: 1.25pt; WIDTH: 9pt; HEIGHT: 23.25pt; MARGIN-LEFT: 89.85pt; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" id=_x0000_s1026 type="#_x0000_t88"></v:shape>HORROR<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 87.25pt; PADDING-RIGHT: 5.4pt; BACKGROUND: red; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=116 noWrap>HORROR<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 87.25pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=116 noWrap>CLASSICS<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 87.25pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=116 noWrap>CLASSICS<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 87.25pt; PADDING-RIGHT: 5.4pt; BACKGROUND: red; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=116 noWrap>ROMANCE<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 87.25pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=116 noWrap>THRILLER<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 87.25pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=116 noWrap>SCIENCE FICTION<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 8"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 87.25pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=116 noWrap>SCIENCE FICTION<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 9; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 87.25pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=116 noWrap>SCIENCE FICTION<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p> </o:p>
<o:p> </o:p>
These two were formatted by conditional formating
These both return a value of 4 and the sub does not find them
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
This one I formated manualy, it returns the correct value
Of 3 and the sub works fine
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,586
Messages
6,179,729
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top