i am having problems with the conditional formating function.
to make document i am working on for office a lot more clear i was hopeing to do some row banding. i think you can get some basic row banding in auto format but i was hoping to do it myself.
when i go to conditional formating - i change the tab to Formula is: ...
I am trying to band columns together in two's and this formula works except for I need the banding to start with column B.
=MOD(INT((COLUMN()-1)/2)+1,2)
Example:
Column A - no banding
Column B and C - banded
Column D and E - no banding
Column F and B - banded
etc.,
Is there another solution to this without maybe adding a helper row below the data with 0 0 1 1 0 0 1 1 0 0 and do the conditional formatting based off of the helper row?
We have a customer rebate in place with various levels of refund based on the quantity purchased during the year. I have used a sumproduct formula to calculate this before.
The customer used to have the following set up -
0-999 - £1.00 per unit rebate.
1000-1999 - £2.00 per unit rebate.
2000-2999 - £3.00 per unit rebate.
So if they bought 2501 units they would get a rebate of (1000*1)+(1000*2)+(501*3). However the customer has trigger points so rather than the above it is now -
0-999 - £1.00 per unit rebate.
1000-1999 - £2.00 per unit rebate.
2000-2999 - £4.00 per unit rebate if 2500 bought.
So now it would look like this - (1000*1)+(1000*2)+(501*4). However if they only bought 2499 units it would be (1000*1)+(1000*2)+(499*2).
I know that if I select say A1:C100 on a spreadsheet, I can color alternating rows using conditional formatting and the formula =MOD( COUNTA($A$1:$A1),2). I am using COUNTA -- rather than simply ROW() -- so that I am only considering visible rows. I am trying to write a VBA subroutine that takes in an arbitrary range and colors in every other row of that range. Something like:
Sub FormatRange(ByRef theRange As Range, ByVal theColor As Integer)
theRange.FormatConditions.Delete
theRange.FormatConditions.Add Type:=xlExpression, Formula1:= "=MOD(COUNTA($A$1:$A1),2)"
theRange.FormatConditions(1).Interior.ColorIndex = theColor
End Sub
However I want to make the argument to COUNTA refer to the first column in theRange, not necessarily column A in the spreadsheet.
I used the method described here:
[url]
The problem is that it seems only to work on the computer on which I made the sheet. This should mean that it is somehow dependent on some local settings. Does anybody know what settings, or whether it is something else that may be the cause?
For obvious reasons, the conditional formatting to shade alternate rows doesn't work when filtered. So I think I need another way of doing it. Luckily, my table is fairly static, rows aren't added or removed. The first column is excluded from the banding. As is the first and last row with data (1 & 67) respectively. I found a relevant thread here, but the code is beyond my understanding.
View 3 Replies View Related