Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trick to Keep Conditional Format Colours?
I applied conditional format to a range of cells in the first column.
Conditional format is based on the number of non-blank cells in each row. I also made a macro to produce a snap report. Macro eventually deletes a bunch of columns and I'm loosing the conditional format. The column with conditionally formatted cells remains intact. Is there any way I can convert the conditional format into regular format in my macro so I can keep the colours on my report? If not, any suggestions? Thanks a lot, Val |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trick to Keep Conditional Format Colours?
If you delete columns, the others should adjust and the colours should
remain okay, even though the formulae would adjust.. What does your data look like, and the CF formula? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Val" wrote in message ... I applied conditional format to a range of cells in the first column. Conditional format is based on the number of non-blank cells in each row. I also made a macro to produce a snap report. Macro eventually deletes a bunch of columns and I'm loosing the conditional format. The column with conditionally formatted cells remains intact. Is there any way I can convert the conditional format into regular format in my macro so I can keep the colours on my report? If not, any suggestions? Thanks a lot, Val |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trick to Keep Conditional Format Colours?
Not if the deleted columns contained the blank cells that were used/counted
to establish what the conditional format color should be. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... If you delete columns, the others should adjust and the colours should remain okay, even though the formulae would adjust.. What does your data look like, and the CF formula? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Val" wrote in message ... I applied conditional format to a range of cells in the first column. Conditional format is based on the number of non-blank cells in each row. I also made a macro to produce a snap report. Macro eventually deletes a bunch of columns and I'm loosing the conditional format. The column with conditionally formatted cells remains intact. Is there any way I can convert the conditional format into regular format in my macro so I can keep the colours on my report? If not, any suggestions? Thanks a lot, Val |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trick to Keep Conditional Format Colours?
Which is why I said should and asked for an indication of the data and the
formulae used. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tom Ogilvy" wrote in message ... Not if the deleted columns contained the blank cells that were used/counted to establish what the conditional format color should be. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... If you delete columns, the others should adjust and the colours should remain okay, even though the formulae would adjust.. What does your data look like, and the CF formula? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Val" wrote in message ... I applied conditional format to a range of cells in the first column. Conditional format is based on the number of non-blank cells in each row. I also made a macro to produce a snap report. Macro eventually deletes a bunch of columns and I'm loosing the conditional format. The column with conditionally formatted cells remains intact. Is there any way I can convert the conditional format into regular format in my macro so I can keep the colours on my report? If not, any suggestions? Thanks a lot, Val |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trick to Keep Conditional Format Colours?
Sorry, I thought the situation was pretty well stated in the original post.
-- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Which is why I said should and asked for an indication of the data and the formulae used. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tom Ogilvy" wrote in message ... Not if the deleted columns contained the blank cells that were used/counted to establish what the conditional format color should be. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... If you delete columns, the others should adjust and the colours should remain okay, even though the formulae would adjust.. What does your data look like, and the CF formula? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Val" wrote in message ... I applied conditional format to a range of cells in the first column. Conditional format is based on the number of non-blank cells in each row. I also made a macro to produce a snap report. Macro eventually deletes a bunch of columns and I'm loosing the conditional format. The column with conditionally formatted cells remains intact. Is there any way I can convert the conditional format into regular format in my macro so I can keep the colours on my report? If not, any suggestions? Thanks a lot, Val |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trick to Keep Conditional Format Colours?
before you delete any cells, in your code, loop down the conditional format
column, count the number of blank cells in each the row and apply the appropriate colorindex to the Interior.ColorIndex property for that row. When the loop is complete then you can remove the conditional formatting so it doesn't interfere and do the current snap report. -- Regards, Tom Ogilvy "Val" wrote in message ... I applied conditional format to a range of cells in the first column. Conditional format is based on the number of non-blank cells in each row. I also made a macro to produce a snap report. Macro eventually deletes a bunch of columns and I'm loosing the conditional format. The column with conditionally formatted cells remains intact. Is there any way I can convert the conditional format into regular format in my macro so I can keep the colours on my report? If not, any suggestions? Thanks a lot, Val |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trick to Keep Conditional Format Colours?
The conditional format is not in the macro. Formulae is
=AND(G1<"",(MOD(COUNTA(G1:DA1),2)=0)) The conditional format basically inverts the font and the background if the number of entries per each row is even and does nothing if it's odd. The macro itself is manipulating with the dates recorded in each row starting from column G and going all the way to column DA. There are some calculations, etc., and in the end I'm deleting all columns from G to DA. Conditional format does not work anymore, of course. P.S. Sorry it took me a while to reply - Was getting home from work... Val "Tom Ogilvy" wrote: before you delete any cells, in your code, loop down the conditional format column, count the number of blank cells in each the row and apply the appropriate colorindex to the Interior.ColorIndex property for that row. When the loop is complete then you can remove the conditional formatting so it doesn't interfere and do the current snap report. -- Regards, Tom Ogilvy "Val" wrote in message ... I applied conditional format to a range of cells in the first column. Conditional format is based on the number of non-blank cells in each row. I also made a macro to produce a snap report. Macro eventually deletes a bunch of columns and I'm loosing the conditional format. The column with conditionally formatted cells remains intact. Is there any way I can convert the conditional format into regular format in my macro so I can keep the colours on my report? If not, any suggestions? Thanks a lot, Val |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trick to Keep Conditional Format Colours?
I think the situation was well established in your original post. You
apparently didn't understand my recommendation that you reproduce that logic in your macro to actually color the cell. I do understand that the conditional formatting was done through the conditional formatting capabilities of the worksheet and you are trying to reproduce the results when you destroy the conditions that produced the color. You don't state what column the conditional formatting is in, but assume G. As an example then: Dim rng as Range, cell as Range, rng1 as Range set rng = Range(Range("G1"),Cells(rows.count,"G").End(xlup)) for each cell in rng cell.Interior.ColorIndex = xlNone set rng1 = Range(cell,cells(cell.row,"DA")) if not isempty(cell) and (application.CountA(rng1) mod 2 = 0) then cell.Interior.ColorIndex = 5 end if Next ' now your code that changes you data. Adjust to suit. -- regards, Tom Ogilvy "Val" wrote in message ... The conditional format is not in the macro. Formulae is =AND(G1<"",(MOD(COUNTA(G1:DA1),2)=0)) The conditional format basically inverts the font and the background if the number of entries per each row is even and does nothing if it's odd. The macro itself is manipulating with the dates recorded in each row starting from column G and going all the way to column DA. There are some calculations, etc., and in the end I'm deleting all columns from G to DA. Conditional format does not work anymore, of course. P.S. Sorry it took me a while to reply - Was getting home from work... Val "Tom Ogilvy" wrote: before you delete any cells, in your code, loop down the conditional format column, count the number of blank cells in each the row and apply the appropriate colorindex to the Interior.ColorIndex property for that row. When the loop is complete then you can remove the conditional formatting so it doesn't interfere and do the current snap report. -- Regards, Tom Ogilvy "Val" wrote in message ... I applied conditional format to a range of cells in the first column. Conditional format is based on the number of non-blank cells in each row. I also made a macro to produce a snap report. Macro eventually deletes a bunch of columns and I'm loosing the conditional format. The column with conditionally formatted cells remains intact. Is there any way I can convert the conditional format into regular format in my macro so I can keep the colours on my report? If not, any suggestions? Thanks a lot, Val |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trick to Keep Conditional Format Colours?
Thanks, Tom.
I now see what you have originally suggested. It'll work with some minor tweaking. I appreciate your help. Regards, Val "Tom Ogilvy" wrote: I think the situation was well established in your original post. You apparently didn't understand my recommendation that you reproduce that logic in your macro to actually color the cell. I do understand that the conditional formatting was done through the conditional formatting capabilities of the worksheet and you are trying to reproduce the results when you destroy the conditions that produced the color. You don't state what column the conditional formatting is in, but assume G. As an example then: Dim rng as Range, cell as Range, rng1 as Range set rng = Range(Range("G1"),Cells(rows.count,"G").End(xlup)) for each cell in rng cell.Interior.ColorIndex = xlNone set rng1 = Range(cell,cells(cell.row,"DA")) if not isempty(cell) and (application.CountA(rng1) mod 2 = 0) then cell.Interior.ColorIndex = 5 end if Next ' now your code that changes you data. Adjust to suit. -- regards, Tom Ogilvy "Val" wrote in message ... The conditional format is not in the macro. Formulae is =AND(G1<"",(MOD(COUNTA(G1:DA1),2)=0)) The conditional format basically inverts the font and the background if the number of entries per each row is even and does nothing if it's odd. The macro itself is manipulating with the dates recorded in each row starting from column G and going all the way to column DA. There are some calculations, etc., and in the end I'm deleting all columns from G to DA. Conditional format does not work anymore, of course. P.S. Sorry it took me a while to reply - Was getting home from work... Val "Tom Ogilvy" wrote: before you delete any cells, in your code, loop down the conditional format column, count the number of blank cells in each the row and apply the appropriate colorindex to the Interior.ColorIndex property for that row. When the loop is complete then you can remove the conditional formatting so it doesn't interfere and do the current snap report. -- Regards, Tom Ogilvy "Val" wrote in message ... I applied conditional format to a range of cells in the first column. Conditional format is based on the number of non-blank cells in each row. I also made a macro to produce a snap report. Macro eventually deletes a bunch of columns and I'm loosing the conditional format. The column with conditionally formatted cells remains intact. Is there any way I can convert the conditional format into regular format in my macro so I can keep the colours on my report? If not, any suggestions? Thanks a lot, Val |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional logic - trick question | Excel Discussion (Misc queries) | |||
is it possible to have more than 3 colours in conditional format | Excel Worksheet Functions | |||
Create conditional IF to format cells using 6 different colours | Excel Worksheet Functions | |||
Another avoid the loop trick request - on fast fill (Maybe an Alan trick) | Excel Programming | |||
Conditional Row Colours | Excel Programming |