PDA

View Full Version : [SOLVED] Can font color or fill color trigger an event?



cmpgeek
12-01-2004, 08:04 AM
is it possible for a macro to make Excel insert a row dependent on the font or fill color of a cell?

as i explained in a previous post, i export results from Crystal Reports to Excel spreadsheets on an almost daily basis. 9 times out of 10, there are extra (empty) rows between each line of data - even if the data should be grouped together.

kpuls and NateO gave me some great suggestions on how to get rid of all the empty rows (thanks guys!); but now i am wanting to find a way to go back through and reinsert an empty row between certain lines...

the particular report i am working on now lists all information on items from a particular product category and is grouped by item description. There are at least 2 -3 lines of information for each item... once i remove all the extra empty rows from the entire report, i want to be able to go back in and insert an empty row between each item (roughly every third line)... what i am wondering if, is i go back in through Crystal and change the font or fill color of a particular field, if once i export the results to Excel, i can remove the extra rows initially, and then have Excel go back through an insert a row below each cell that has in red text or has a colored fill...

i hope this is making some sense... please let me know if i need to explain it better...

Killian
12-01-2004, 09:10 AM
Well, why don't you amend the code you already have for deleting empty rows to test for fill color or font color properties and insert rows accordingly? I guess it depends how your deleting the rows so maybe you should post your code
I notice the post title is can font color or fill color trigger an event?
Intrestingly, no it doesn't fire the Worksheet_Change event - that seems a bit strange to me since the worksheet is changing, but there it is!

cmpgeek
12-01-2004, 11:01 AM
here is the code that NateO gave me to remove the empty rows:



Sub DellTime()
Dim myRng As Range
Application.ScreenUpdating = False
On Error Resume Next
With Sheets(1)
.AutoFilterMode = False
Set myRng = Range(.Cells(2, 1), .Cells(65336, 1).End(xlUp))
myRng.AutoFilter Field:=1, Criteria1:="="
myRng.SpecialCells(xlVisible).EntireRow.Delete
.AutoFilterMode = False
With .Range("a1")
If Not CBool(Len(.Value)) Then .EntireRow.Delete
End With
End With
Set myRng = Nothing
Application.ScreenUpdating = True
End Sub


i am not smart enough to completely understand this yet - but i know that it works LOL :giggle

Brandtrock
12-01-2004, 05:52 PM
Are you able to modify your Crystal Report? There really isn't a good reason to be exporting lines that you don't need. I've used Crystal extensively and never had to worry about this type of thing.

While the workaround that you are trying to get done should be no problem for the coders here, there isn't much reason to do it if it can be avoided.

If you want to e-mail a copy of the report to me (see my profile) I'll tryto take a look at it for you.

If not, okay too.

Regards,

cmpgeek
12-02-2004, 08:25 AM
i have gotten rid of everything i possibly can to avoid the empty rows... i have even put all the fields in one long line across the screen so there was absolutely no extra space below them and shrunk them down where you can not read them completely on the Crystal Preview - i still get two empty rows... i will send the report in just a minute... thanks for the offer...