Excel problem driving me nuts!

  cedricthecat 14:22 20 Oct 2006

Been searching Google and trying all sorts of workarounds on this for ages, but getting nowhere....

What I've got is a pretty messy spreadsheet, which we use to show all sorts of stuff. It's messy as it covers four laptops capturing image data "out and about" operated by four individuals, with the images being assessed by three individuals!

Anyway...! We sync the laptops as and when and I need to check how many days of images are outstanding. Columns E, F, G & H show when the units were used, by adding the initials of the operator into the column, with a conditional format from Column X changing the background of the cell in E-F to grey on days when the units are synced. What I need is some way to add up the total of any cell with data in BELOW the one that has been changed to grey. Sorry if this is not clear!

Is there any way to do this without resorting to macros?


  VoG II 14:53 20 Oct 2006

I don't think that you can do this without VBA. You can get the last occupied row in Column E using

Lastrow = Cells(Rows.Count, 5).End(xlUp).Row

then have a loop that counts 'backwards' from that row until a grey shaded cell is encountered. click here gives some examples of VBA for identifying colours.

  cedricthecat 15:02 20 Oct 2006

Hmmm. I guess you are probably right :-(

Worried that the grey cell identifier won't work as I'm sure I've read somethere that it will only recognise the native colour of the cell, not one applied by conditional formatting??

Did wonder if I could use the value in X to stop any backwards/upwards counting?

Or... Create a new column, and use a formula which placed the date (from same row) in it if row X had an entry, and a 1 if cell in Column E or F or G or H had an entry on same row.

I managed to make it do this, and ended up with a column of blanks, dates (formatted into numbers) and 1's. I'd hoped to do a sort of backward lookup to count only the 1's and only those 1's below the large number generated by the date....

(NB. Knew you'd be on the case VoG! You've helped me before on the same spreadsheet, but under my "Monty Burns" logon - at work at the mo and can't remember my password!)

  VoG II 15:15 20 Oct 2006

You could use the same logic in the macro as is used in the conditional format to identify the 'magic' cell. So for example if the idea was to count upwards from the bottom of Column E to where En = Xn:

Sub tst()
Dim Lastrow As Long, iRow As Long, Kount
Kount = 0
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For iRow = Lastrow To 1 Step -1
If Cells(iRow, 5).Value = Cells(iRow, 24).Value Then
'match found; display count and exit
MsgBox Kount
Exit Sub
Kount = Kount + Cells(iRow, 5).Value
End If
Next iRow
End Sub

  cedricthecat 15:52 20 Oct 2006

This looks good, although I'm not sure if I really understand it!!

My brain hurts too much to try it today, but I'll give it a go first thing Monday!

Thanks very much!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

InVision Studio takes on Adobe XD and Sketch

Camera tips to take better iPhone photos

Comment transformer un iPhone en borne Wi-Fi ?