conditional formatting with vba

Hello everyone,
this macro:

Please Login or Register  to view this content.

need for conditional formatting of the range E4: AI39.
I ask for help for a change.
Deleting a volore inserted for example "A" the cell must go with the original color, must not remain yellow.
I hope I explained.
max_max

-----------------------------------------------------------------------------------------------------------

Ciao a tutti,
questa macro:

,,
,,
,,
,,
,,

serve per la formattazione condizionale del range E4:AI39.
Io chiedo un aiuto per una modifica.
Cancellando un volore inserito per esempio A la cella deve tornare con il colore originale, non deve restare gialla.
Io spero di essermi spiegato.
max_max

Hi, may_max,

CF will override your formatting on the coloured rows - you would need to use VBA as well to get the colours on and off.

Please Login or Register  to view this content.

You should restrict the code to work for only those cells invoked and not the whole range.

Ciao,
Holger

Hi, max_max,

exactly what he codeline

Please Login or Register  to view this content.

wants the code to do.

You would need to work with areas in order to get the code working for more than one cell like

Please Login or Register  to view this content.

as you have not made clear if you have a continuous number of cells or if these might be single cells as well.

Ciao,
Holger

Hi, max_max,

when I tested the code before posting here I used single cells and thus worked with Selection.

Ciao,
Holger

Hi, max_max,

sorry could you tell me where the code isn´t working? If it´s inside the range filled by CF that´s normal due to you mixing up standard features and VBA with CF being on top of the list and not allowing changes if the cell is coloured..

Ciao,
Holegr

Conditional Formatting > = MOD (ROW (), 2) = 1
range A4:AJ39
formatting to alternating rows colored
----------------------------------------------------------
Formattazione condizionale > =RESTO(RIF.RIGA();2)=1
range A4:AJ39
formattazione per righe colorate alternate

Hi, max_max,

again: if you apply Conditional Formatting this will overwrite any VBA code and colours. In addition I can´t see any reason why CF is being used on a static range instead of switching to VBA for doing this and getting the proper results (at least what I understand from the request).

Code for colouring the range:

Please Login or Register  to view this content.

Code behind the Sheet:

Please Login or Register  to view this content.

Ciao,
Holger

Hi, max_max,

you would need to explain what you expect the code to do and what it´s not doing although I suspect that you would need to run another code once before you apply the Worksgheet_Change-event. But you never mentioned that and I´m afraid I ran out of guessing of what users may think the code should be doing woithout explicitely mention it.

Ciao,
Holger

Hi, max_max,

please change the macro to set up the sheet in the sample to

Please Login or Register  to view this content.

As long as macros are enabled when working with the workbook the Worksheet_Change-event should handle any changes with the area. That´s why I went away from running through the entire area on each change to any cell in the area and tried to narrow down the number of cells to be checked.

Ciao,
Holger

Hi, max_max,

tested my latest code in addition with the Worksheet_Change-event in my uploaded workbook and everything worked out fine (both in Excel2013 as well as in Excel2003) when Macros are activated. Please test if any single colour is added as I suspect you may have turned off events, or make clear that no other events in ThisWorkbook or behind the sheet are active.

Ciao,
Holger

Hi, max_max,

there are 2 procedures (one of them coloring the area, one taking the job to colour/clear the selection). You used the procedure to colour down the whole area as one Worksheet_Change-event (never was planned to act as that) which produces exactly the "error" that you describe as the code only refers to filled cells (while the original Worksheet_Change-code handles areas which are altered).

ColourLinesContents goes into a standard modul and is only used to colour in the values (this means just once!), Worksheet_Change goes behind the sheet.

Ciao,
Holger

Hi, max_max,

exactrly like that, and you only run the code from Module1 once (you may change it a bit to take care of the additional colour which have not been taken away, I added that to the codelines). So the codes may look like

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The first code is to get the area set up with the values inserted, the Worksheet_Change-event will take care of any changes in the area.

Ciao,
Holger

Hi, max_max,

I have no idea what you are doing to make a working code fail.

Ciao,
Holger

Hello hahobe,
after many tests I hold valid only this part:

Please Login or Register  to view this content.

works well.
There are many sheets in the workbook all the same and you have to insert the macro in each sheet and workbook becomes too big so you have to put the macro in the ThisWorkbook.
How can I change?
I hope I explained beings.
Thank you.
xam_xam