Dear all,
Attached a very small and meaningless XLSM sheet with some conditional formatting.
This is an example for a larger sheet (about 700 rows and 23 columns). In reality it is
is generated from a database.
Requirements:
- several columns need to be conditionally formatted
- some the the conditional formats depend on values in other columns: the most right ones.
- the sheet has to be handed over to a laptop and will be used by elder digital illiterate people
- so I convert the condition format to permanent format and
- I remove the conditional formats as well as the columns (rightmost ones) they are based upon. on the laptop they need a larger and bolded font and all columns must be on the screen without scrolling. (Í'll leave out the functional requirements behind it).
- the sheet is in "table"format for convenience
I had in mind to convert conditional format to permanent with
Selection.Interior.Color = Selection.DisplayFormat.Interior.Color.
But that did not work: it converts the Selection.Interior.Color to 0 (so black!)
I had to use the less elegant loop over cells to get it working, but I always try to write my code elegantly - as above.:
For Each Cell In Selection
Cell.Interior.Color = Cell.DisplayFormat.Interior.Color
Next Cell
For this example I used the same code for the 1st 2 columns, based on values in the 3 column: run macro CondFormat to understand it possibly better.
I expected my 1st attempt also working: Selection is a Range as well isn't it?
So please comment,
Best regards, Hans Troost
Bookmarks