Hi to all.
I have workbook with 31 sheets,and 60 rows per sheet. In column 2 i have put in the letter "D". What i am after is, if the letter "D" is entered that a macro or vba looks the last 2 sheets for that cell for letter "D". If so color it red. This can only work between rows 11 and 60.
I hope i was clear with the example
Allan
'What you need is a two dimensional array to store the previous values.
'I haven't tested it, but you could try something like this.
You may want to run a macro to list the sheets first, i.e. cells(n,1) = s.name as they may be stored in a different order than they appear in the workbook.
Dim D(1000,1000) 'Two dimensional data array For each s In Worksheets n = n + 1 For each c in range("B11:B60") m = m + 1 D(n,m) = c.value Next c Next s 'Then you can iterrogate D for the cell in question For y = 1 to 100 If cells(y,2) = "D" then If D(n-1,y) ="D" And D(n-2,y) = "D" then Cells(y,2).interior.colorindex = 3 End if End if Next y
Last edited by VBA Noob; 11-22-2007 at 06:13 AM.
Phil
Thanks For the reply
the sheets run from 1 to 31
I have it in a sheet selection change module.
Sheets 1,2 allow the "D". the third gives me a runtime error of 9, However if i do the same on sheets 3,4 it allows "d" on sheet 5 but does nothing. I realize you said it was not tested and thats fine. It stops at:
D(n, m) = c.Value on sheet3.
Could it be that the "D" is entered by a userform command button rather than text input.
anyways thanks i will tinker with what you gave me.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks