Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show cells with formulas without permanently changing the cells
I want to create an On/Off button that will temporarily show which cells
contain formulas. If someone knows a better way pls say so. I thought of adding and then subtracting a fixed number from the Interior.color property but I don't know how to retrieve the current value. rngFormulas.Select If flag = False Then With Selection.Interior .ColorIndex = ????? + 300000 End With flag = True Else With Selection.Interior .ColorIndex = ????? - 300000 End With flag = False End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show cells with formulas without permanently changing the cells
This doesn't incorporate your flag so adjust if you need that.
Select either a single cell to get formulas in whole sheet or a selection of cells. Sub ToggleFormulaColour() Dim rng As Range, vFntClrIdx On Error Resume Next Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23) On Error GoTo errH If rng Is Nothing Then MsgBox "No Formulas" Else vFntClrIdx = rng.Font.ColorIndex If IsNull(vFntClrIdx) Then vFntClrIdx = -1 If vFntClrIdx 0 Then vFntClrIdx = xlAutomatic Else vFntClrIdx = 5 'blue in a default palette End If rng.Font.ColorIndex = vFntClrIdx End If errH: End Sub Note SpecialCells in VBA fails if a little over 8000 discontiguous areas are involved. Also try Ctrl-`¬¦ the key under Esc. Regards, Peter T "DoctorG" wrote in message ... I want to create an On/Off button that will temporarily show which cells contain formulas. If someone knows a better way pls say so. I thought of adding and then subtracting a fixed number from the Interior.color property but I don't know how to retrieve the current value. rngFormulas.Select If flag = False Then With Selection.Interior .ColorIndex = ????? + 300000 End With flag = True Else With Selection.Interior .ColorIndex = ????? - 300000 End With flag = False End If |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show cells with formulas without permanently changing the cells
Hey Doc
Very easy... just create the buttons, and in the code module paste this line for viewing all the cells that contain formulas... ActiveWindow.DisplayFormulas = True To hide the formulas, just insert this line... ActiveWindow.DisplayFormulas = False Let me know if you win. DoctorG wrote: I want to create an On/Off button that will temporarily show which cells contain formulas. If someone knows a better way pls say so. I thought of adding and then subtracting a fixed number from the Interior.color property but I don't know how to retrieve the current value. rngFormulas.Select If flag = False Then With Selection.Interior .ColorIndex = ????? + 300000 End With flag = True Else With Selection.Interior .ColorIndex = ????? - 300000 End With flag = False End If |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show cells with formulas without permanently changing the cell
Thanks for this tip.
I am already aware of the Display Formulas Option but I wanted something that I could use in programming. I was presented with a 7500 line spreadsheet with mixed formula and text/value content. It was a nightmare just looking at. The changed background would "position" the eye easier to the cells needing attention. Not to mention the fact that I could trap a certain attribute or property and filter the column. I needed knowledge on conditional range and property handling. Thanks anyway! " wrote: Hey Doc Very easy... just create the buttons, and in the code module paste this line for viewing all the cells that contain formulas... ActiveWindow.DisplayFormulas = True To hide the formulas, just insert this line... ActiveWindow.DisplayFormulas = False Let me know if you win. DoctorG wrote: I want to create an On/Off button that will temporarily show which cells contain formulas. If someone knows a better way pls say so. I thought of adding and then subtracting a fixed number from the Interior.color property but I don't know how to retrieve the current value. rngFormulas.Select If flag = False Then With Selection.Interior .ColorIndex = ????? + 300000 End With flag = True Else With Selection.Interior .ColorIndex = ????? - 300000 End With flag = False End If |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show cells with formulas without permanently changing the cell
Peter I believe your approach assumes a single Font Color for the whole
range. I am asking for a way to change the background color (it is easier to spot) back and forth regardless if it is the same for all cells or not. That is why I am looking for a way to change each cell color individually. Is this possible? "Peter T" wrote: This doesn't incorporate your flag so adjust if you need that. Select either a single cell to get formulas in whole sheet or a selection of cells. Sub ToggleFormulaColour() Dim rng As Range, vFntClrIdx On Error Resume Next Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23) On Error GoTo errH If rng Is Nothing Then MsgBox "No Formulas" Else vFntClrIdx = rng.Font.ColorIndex If IsNull(vFntClrIdx) Then vFntClrIdx = -1 If vFntClrIdx 0 Then vFntClrIdx = xlAutomatic Else vFntClrIdx = 5 'blue in a default palette End If rng.Font.ColorIndex = vFntClrIdx End If errH: End Sub Note SpecialCells in VBA fails if a little over 8000 discontiguous areas are involved. Also try Ctrl-`¬¦ the key under Esc. Regards, Peter T "DoctorG" wrote in message ... I want to create an On/Off button that will temporarily show which cells contain formulas. If someone knows a better way pls say so. I thought of adding and then subtracting a fixed number from the Interior.color property but I don't know how to retrieve the current value. rngFormulas.Select If flag = False Then With Selection.Interior .ColorIndex = ????? + 300000 End With flag = True Else With Selection.Interior .ColorIndex = ????? - 300000 End With flag = False End If |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show cells with formulas without permanently changing the cell
Did you actually try the example.
If you want background fill change vFntClrIdx = rng.Font.ColorIndex to vFntClrIdx = rng.Interior.ColorIndex and xlAutomatic to xlNone I don't understand why you want to process each cell individually Regards, Peter T "DoctorG" wrote in message ... Peter I believe your approach assumes a single Font Color for the whole range. I am asking for a way to change the background color (it is easier to spot) back and forth regardless if it is the same for all cells or not. That is why I am looking for a way to change each cell color individually. Is this possible? "Peter T" wrote: This doesn't incorporate your flag so adjust if you need that. Select either a single cell to get formulas in whole sheet or a selection of cells. Sub ToggleFormulaColour() Dim rng As Range, vFntClrIdx On Error Resume Next Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23) On Error GoTo errH If rng Is Nothing Then MsgBox "No Formulas" Else vFntClrIdx = rng.Font.ColorIndex If IsNull(vFntClrIdx) Then vFntClrIdx = -1 If vFntClrIdx 0 Then vFntClrIdx = xlAutomatic Else vFntClrIdx = 5 'blue in a default palette End If rng.Font.ColorIndex = vFntClrIdx End If errH: End Sub Note SpecialCells in VBA fails if a little over 8000 discontiguous areas are involved. Also try Ctrl-`¬¦ the key under Esc. Regards, Peter T "DoctorG" wrote in message ... I want to create an On/Off button that will temporarily show which cells contain formulas. If someone knows a better way pls say so. I thought of adding and then subtracting a fixed number from the Interior.color property but I don't know how to retrieve the current value. rngFormulas.Select If flag = False Then With Selection.Interior .ColorIndex = ????? + 300000 End With flag = True Else With Selection.Interior .ColorIndex = ????? - 300000 End With flag = False End If |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show cells with formulas without permanently changing the cell
Because formula cells might have different background colors...
"Peter T" wrote: Did you actually try the example. If you want background fill change vFntClrIdx = rng.Font.ColorIndex to vFntClrIdx = rng.Interior.ColorIndex and xlAutomatic to xlNone I don't understand why you want to process each cell individually Regards, Peter T "DoctorG" wrote in message ... Peter I believe your approach assumes a single Font Color for the whole range. I am asking for a way to change the background color (it is easier to spot) back and forth regardless if it is the same for all cells or not. That is why I am looking for a way to change each cell color individually. Is this possible? "Peter T" wrote: This doesn't incorporate your flag so adjust if you need that. Select either a single cell to get formulas in whole sheet or a selection of cells. Sub ToggleFormulaColour() Dim rng As Range, vFntClrIdx On Error Resume Next Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23) On Error GoTo errH If rng Is Nothing Then MsgBox "No Formulas" Else vFntClrIdx = rng.Font.ColorIndex If IsNull(vFntClrIdx) Then vFntClrIdx = -1 If vFntClrIdx 0 Then vFntClrIdx = xlAutomatic Else vFntClrIdx = 5 'blue in a default palette End If rng.Font.ColorIndex = vFntClrIdx End If errH: End Sub Note SpecialCells in VBA fails if a little over 8000 discontiguous areas are involved. Also try Ctrl-`¬¦ the key under Esc. Regards, Peter T "DoctorG" wrote in message ... I want to create an On/Off button that will temporarily show which cells contain formulas. If someone knows a better way pls say so. I thought of adding and then subtracting a fixed number from the Interior.color property but I don't know how to retrieve the current value. rngFormulas.Select If flag = False Then With Selection.Interior .ColorIndex = ????? + 300000 End With flag = True Else With Selection.Interior .ColorIndex = ????? - 300000 End With flag = False End If |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show cells with formulas without permanently changing the cell
Is there some other property you can change, eg pattern, font bold, .size
etc In your OP you mentioned changing the colour index by some factor (not 300000 as colour index's are only in the region 1-56 and two -ve numbers), if you have multiple format colours how would you differentiate which are original & which temporarily changed. Something along the lines of your objective is very doable but I can only suggest consider the logic as to how you want to do that whilst retaining the possibility to reset your original formats. Regards, Peter T "DoctorG" wrote in message ... Because formula cells might have different background colors... "Peter T" wrote: Did you actually try the example. If you want background fill change vFntClrIdx = rng.Font.ColorIndex to vFntClrIdx = rng.Interior.ColorIndex and xlAutomatic to xlNone I don't understand why you want to process each cell individually Regards, Peter T "DoctorG" wrote in message ... Peter I believe your approach assumes a single Font Color for the whole range. I am asking for a way to change the background color (it is easier to spot) back and forth regardless if it is the same for all cells or not. That is why I am looking for a way to change each cell color individually. Is this possible? "Peter T" wrote: This doesn't incorporate your flag so adjust if you need that. Select either a single cell to get formulas in whole sheet or a selection of cells. Sub ToggleFormulaColour() Dim rng As Range, vFntClrIdx On Error Resume Next Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23) On Error GoTo errH If rng Is Nothing Then MsgBox "No Formulas" Else vFntClrIdx = rng.Font.ColorIndex If IsNull(vFntClrIdx) Then vFntClrIdx = -1 If vFntClrIdx 0 Then vFntClrIdx = xlAutomatic Else vFntClrIdx = 5 'blue in a default palette End If rng.Font.ColorIndex = vFntClrIdx End If errH: End Sub Note SpecialCells in VBA fails if a little over 8000 discontiguous areas are involved. Also try Ctrl-`¬¦ the key under Esc. Regards, Peter T "DoctorG" wrote in message ... I want to create an On/Off button that will temporarily show which cells contain formulas. If someone knows a better way pls say so. I thought of adding and then subtracting a fixed number from the Interior.color property but I don't know how to retrieve the current value. rngFormulas.Select If flag = False Then With Selection.Interior .ColorIndex = ????? + 300000 End With flag = True Else With Selection.Interior .ColorIndex = ????? - 300000 End With flag = False End If |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show cells with formulas without permanently changing the cell
Peter first of all thanks a lot for your time and effort to help me.
I have tried to think of other properties to change i.e. I tried to add a comment on every formula cell saying "This is a Formula", since I seldom use comments on formula cells. I couldn't get that to work either (AddComment etc.) Btw, it IS possible to add and subtract a value such as 30000 to the ..Interior.Color property. It is probably dealt as a hex number. All I can tell you is that Excel 2003 allows me to change between i.e. 7 and 30007, and it produces different color shades. That is why I thought of "shifting" all ..Color values up and then down again. This method would leave the original color intact after 2 runs (on/off). What I don't know is how to process the cells in the ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) selection one at a time, querying its .Interior.Color value, changing it and then proceeding with the next one. If a single action is performed on all cells at once I believe it is impossible. "Peter T" wrote: Is there some other property you can change, eg pattern, font bold, .size etc In your OP you mentioned changing the colour index by some factor (not 300000 as colour index's are only in the region 1-56 and two -ve numbers), if you have multiple format colours how would you differentiate which are original & which temporarily changed. Something along the lines of your objective is very doable but I can only suggest consider the logic as to how you want to do that whilst retaining the possibility to reset your original formats. Regards, Peter T "DoctorG" wrote in message ... Because formula cells might have different background colors... "Peter T" wrote: Did you actually try the example. If you want background fill change vFntClrIdx = rng.Font.ColorIndex to vFntClrIdx = rng.Interior.ColorIndex and xlAutomatic to xlNone I don't understand why you want to process each cell individually Regards, Peter T "DoctorG" wrote in message ... Peter I believe your approach assumes a single Font Color for the whole range. I am asking for a way to change the background color (it is easier to spot) back and forth regardless if it is the same for all cells or not. That is why I am looking for a way to change each cell color individually. Is this possible? "Peter T" wrote: This doesn't incorporate your flag so adjust if you need that. Select either a single cell to get formulas in whole sheet or a selection of cells. Sub ToggleFormulaColour() Dim rng As Range, vFntClrIdx On Error Resume Next Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23) On Error GoTo errH If rng Is Nothing Then MsgBox "No Formulas" Else vFntClrIdx = rng.Font.ColorIndex If IsNull(vFntClrIdx) Then vFntClrIdx = -1 If vFntClrIdx 0 Then vFntClrIdx = xlAutomatic Else vFntClrIdx = 5 'blue in a default palette End If rng.Font.ColorIndex = vFntClrIdx End If errH: End Sub Note SpecialCells in VBA fails if a little over 8000 discontiguous areas are involved. Also try Ctrl-`¬¦ the key under Esc. Regards, Peter T "DoctorG" wrote in message ... I want to create an On/Off button that will temporarily show which cells contain formulas. If someone knows a better way pls say so. I thought of adding and then subtracting a fixed number from the Interior.color property but I don't know how to retrieve the current value. rngFormulas.Select If flag = False Then With Selection.Interior .ColorIndex = ????? + 300000 End With flag = True Else With Selection.Interior .ColorIndex = ????? - 300000 End With flag = False End If |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show cells with formulas without permanently changing the cell
I think you are confusing the Color property with ColorIndex (you mentioned
the latter in your OP). Cell colour formats can only accept colours from the 56 colour palette or a couple of system colours, ie .ColorIndex. Each palette colour is stored as a long 0-16777215. If you try and apply your own long colour number it will map to one of the colours assigned to a colorindex. Incrementing a long colour by say 30000 will not shift the colour by an intuitive amount, firstly as I've already said it will map to the nearest colorindex colour, but even if not the long colour is actually R + G * 256 + B * 256 * 256 where RGB are 0-255 30000 is a relatively small difference depending on the type of colour, may well remap to the original colour, perhaps say while adding but not if subtracting. Unpredictable results may occur if you try applying a long colour outside the 0-16777215 range. What I don't know is how to process the cells in the ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) selection one at a time, Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23) For each cel in rng cel.interior.colorindex = blah If you want to contact me off-line I have something in development that might sort you out. Regards, Peter T pmbthornton gmail com "DoctorG" wrote in message ... Peter first of all thanks a lot for your time and effort to help me. I have tried to think of other properties to change i.e. I tried to add a comment on every formula cell saying "This is a Formula", since I seldom use comments on formula cells. I couldn't get that to work either (AddComment etc.) Btw, it IS possible to add and subtract a value such as 30000 to the .Interior.Color property. It is probably dealt as a hex number. All I can tell you is that Excel 2003 allows me to change between i.e. 7 and 30007, and it produces different color shades. That is why I thought of "shifting" all .Color values up and then down again. This method would leave the original color intact after 2 runs (on/off). What I don't know is how to process the cells in the ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) selection one at a time, querying its .Interior.Color value, changing it and then proceeding with the next one. If a single action is performed on all cells at once I believe it is impossible. "Peter T" wrote: Is there some other property you can change, eg pattern, font bold, ..size etc In your OP you mentioned changing the colour index by some factor (not 300000 as colour index's are only in the region 1-56 and two -ve numbers), if you have multiple format colours how would you differentiate which are original & which temporarily changed. Something along the lines of your objective is very doable but I can only suggest consider the logic as to how you want to do that whilst retaining the possibility to reset your original formats. Regards, Peter T "DoctorG" wrote in message ... Because formula cells might have different background colors... "Peter T" wrote: Did you actually try the example. If you want background fill change vFntClrIdx = rng.Font.ColorIndex to vFntClrIdx = rng.Interior.ColorIndex and xlAutomatic to xlNone I don't understand why you want to process each cell individually Regards, Peter T "DoctorG" wrote in message ... Peter I believe your approach assumes a single Font Color for the whole range. I am asking for a way to change the background color (it is easier to spot) back and forth regardless if it is the same for all cells or not. That is why I am looking for a way to change each cell color individually. Is this possible? "Peter T" wrote: This doesn't incorporate your flag so adjust if you need that. Select either a single cell to get formulas in whole sheet or a selection of cells. Sub ToggleFormulaColour() Dim rng As Range, vFntClrIdx On Error Resume Next Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23) On Error GoTo errH If rng Is Nothing Then MsgBox "No Formulas" Else vFntClrIdx = rng.Font.ColorIndex If IsNull(vFntClrIdx) Then vFntClrIdx = -1 If vFntClrIdx 0 Then vFntClrIdx = xlAutomatic Else vFntClrIdx = 5 'blue in a default palette End If rng.Font.ColorIndex = vFntClrIdx End If errH: End Sub Note SpecialCells in VBA fails if a little over 8000 discontiguous areas are involved. Also try Ctrl-`¬¦ the key under Esc. Regards, Peter T "DoctorG" wrote in message ... I want to create an On/Off button that will temporarily show which cells contain formulas. If someone knows a better way pls say so. I thought of adding and then subtracting a fixed number from the Interior.color property but I don't know how to retrieve the current value. rngFormulas.Select If flag = False Then With Selection.Interior .ColorIndex = ????? + 300000 End With flag = True Else With Selection.Interior .ColorIndex = ????? - 300000 End With flag = False End If |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show cells with formulas without permanently changing the cells
I've found this to be useful: http://www.xl-logic.com/xl_files/vba/color_code.zip You can recover the original formatting by copying it to a blan worksheet and copying it back once you've analysed the formalae. Co -- colofnatur ----------------------------------------------------------------------- colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435 View this thread: http://www.excelforum.com/showthread.php?threadid=56241 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formulas with changing cells | Excel Discussion (Misc queries) | |||
Copying formulas with changing cells | Excel Discussion (Misc queries) | |||
changing signs in formulas and cells | Excel Discussion (Misc queries) | |||
Moving cells without changing their formulas | Excel Worksheet Functions | |||
How do you show formulas in certain cells only (not the whole she. | Excel Worksheet Functions |