Automatic Highlight On Cues

[seeke]

I'm having to highlight different groups of rows (thousands, or 10 thousands of rows). It seems like I would need a script to do this quickly, and I have no knowledge of scripting.

Here's what I want to do:

Layout of my sheet: On Column A, there are 3 types of cells: letters, a "#" (yes, just a "#" in that whole cell), then blank. So the column would go like this: a cell of letters, a cell of # (right underneath), cell of blank, cell of blank, blank... then a new cell of letters, then a "#" underneath, then so on and so far.
What prevents me from setting a automatic highlighting after a certain of rows is because of number of rows with blanks varies (sometime, 100 of them), meaning there is a variable gap between the Cell of letters and cell of "#".

I hope there is some way to write a code for automatic highlighting: a color for the whole group of [cell of letters, cell of #, then as many cell of blanks] under them till there's a new cell of letters. To clarify, there will be alternating color of each group of [cell of letters, cell of #, cells of blanks]; and the whole rows

With my non-existent coding experience, I'm thinking the "#" could act as anchor for the highlighting script.

Linked is an image of the sample sheet; I'm using excel 2010 windows, and the 2 highlight colors are "Note" and "Input";

Imageshack - ashampoosnap2012090923h.png

Thank you very much in advance!

-S

 

[Asala42]

Here's a method without using VBA. (Conditional formatting with a helper column).

Based on your screenshot, I'll assume your data is within A1:D1000 for example purposes.

- Highlight the data range and format it one of your color fills (say light orange). Add gridlines.
- In E1, type the number 1
- In E2, write the formula: =IF(D3="Message",E1*-1,E1)
- Copy down the length of your data. You can hide this column if you wish.
If things are going right, you should notice that the 1s and -1s should correspond to your groups of data that you want to color differently.

- Highlight the data range, starting with A1
- Conditional Formatting> Manage Rules > New Rule >Use formula: =$E1=1, format fill as the other color (light yellow).
- Done

In later versions of the file, you can simply copy the formula and formatting down to the length of data you have.

 

[seeke]

Thank you so much Asa. It works flawlessly .

Please have a beer on me if we do meet!
-S