Changing colors in specific cells for printing

[Dan15]

When printing the spreadsheet, I am looking for the code to search through a column and where there is a number entered, color the font to the appropriate 2 or 3 cell values to the left white. This is for creating proposals, and will leave decriptions without showing the price.

Thank you-

-Dan

 

[Zathras]

The easiest way may be to use conditional formatting.

1. Designate a cell to use as a "switch"
2. Give it a meaningful range name like "BLANKPRICE"
3. Select the column that contains the prices.
4. Select Format/Conditional Formatting... from the menu.
5. Change the selection in the combo box from "Cell Value Is" to "Formula Is"
6. Type the following into the text box: =BLANKPRICE=1
7. Click the Format... button
8. In the "Color:" combo box, select white.
9. Click Ok (twice)

when the switch cell contains the value 1, the prices will not print. Any other value will allow the prices to show.

Hope this helps.

 

[Dan15]

The only problem with this solution is that it applies to the entire column. Can this be isolated to part of the column? If this function does not solve it, the code to do this should be relatively easy. I can set up the trigger cells in a column. When the trigger cell = 1, then the color of the associated prices is set to white. The code then moves to the next line until it is through the spreadsheet. Unfortunately, knowing the syntax is my problem.

Thank you-

-Dan

 

[Zathras]

I was showing you the simplest possible way. When set manually, conditional formatting is applied to all selected cells.

Since this is the VBA forum, you may wish to set the conditional formatting with code. In that case, the following may give you some idea of how to proceed. All you need to do is execute the SetBlankPriceFormat( ) subroutine for every range that contains a price.
====================================================

Option Explicit
Sub Test()
  SetBlankPriceFormat Selection
End Sub

Sub SetBlankPriceFormat(PriceRange As Range)
' Set conditional formatting to all cells in specified range
' to display/print as white text (non-display) when the
' cell named BLANKPRICE is set to 1.

With PriceRange
  .FormatConditions.Delete
  .FormatConditions.Add Type:=xlExpression, _
      Formula1:="=BLANKPRICE=1"
  .FormatConditions(1).Font.ColorIndex = 2
End With

End Sub

======================================================

The test example works on the selected cells. If you know specific ranges that have prices, you could use something like this:

=======================================================

:
:
( your macro )
:
:
SetBlankPriceFormat Range("G2:G15")
SetBlankPriceFormat Range("G18:G30")
:
:
( more of your macro )
:
:

===================================================

Depending on how your macro is written, there may be other
techniques that could be used. E.g. Offset.

If you need more specific assistance, please provide more specific details of the problem you are trying to solve.

 

[Dan15]

That looks like what I need, unfortunately I am just learning the coding here. Instead of specific cells that have the prices, I will need to check through the entire list.

Thank you for your help