VBA code for Conditional Formatting?

[DD999]

I've created two buttons. One adds subtotals to a worksheet and conditionally formats the sheet to highlight any totals that don't add up to 100. The other removes the subtotals and resets the formatting to normal.

The reset option is working fine, but I can't get the function that formats the subtotaled data to work properly.

I'm confused on how to format a multi-row range of cells
based on the value in a cell at the beginning of each row.

The entire range of data is in a range named Sch_Data so I started witha

Range("Sch_Data").Select

The first cell of each line ends in the word Total.

So the formatting condition should be something like
AND(RIGHT($A5,5) ="Total",ActiveCell <>100)

But I don't think that ActiveCell is the right way to reference the cells to be formatted.

How should I be doing this?

Thanks in advance.

 

[SkipVought]

Hi,

The FORMAT formula needs a REFERENCE ADDRESS. If the ActiveCell is the correct reference, then its the ADDRESS that you need, something like this in your code...

"AND(RIGHT($A5,5) ="""Total"""," & ActiveCell.Address & " <>100)"

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[cLFlaVA]

Couldn't you also just use Conditional Formatting across this range of cells, so that if the value does not equal 100, it's one format, otherwise, it's another?

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[xlbo]

For future reference, please post VBA questions in the VBA forum

Other than that, your answer is in your own example. You have already used:
AND(RIGHT($A5,5) ="Total"

You just need to apply the same concept to the value - If you refer to the rows relatively, you can state a specific start point and excel will figure the rest out so:
AND(RIGHT($A5,5) ="Total",$A5 <>100)

should do the trick for you. As to how to write it in VBA - easiest way to find out is to use the Macro Recorder





Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[DD999]

Hmm....

Thanks for the responses, but I must still be missing something as I can't get it to work. Below is the code I'm using in macro form.

' CondFormat Macro
'
Range("Sch_Data").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RIGHT($A7,5)=""Total"""

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="100"

With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
Selection.FormatConditions(1).Interior.Pattern = xlNone

End Sub

I think the problem is arising form the fact that I'm not properly comparing the value in cell A7 with the other cells in row 7, the value in cell A8 with the other cells in row 8, etc.

Also, it seems like the Interior.Pattern only applies to 1
condition and not both?