To get around Conditional formatting limit

[dantonic]

Hello, I am trying to piece together code that I've stumbled upon, but cannot get this to work...

I need to be able to set a particular background color for a column of cells based on values in other adjacent cells in the same row.

The values that are being evaluated are percentage values (the format is XX%). The limit can change, and is located in column -6, and looks like this: >= 95%. I'm using the RIGHT function to attempt to only grab the first 3 characters from the right: 95%.
I have a very basic understanding of programming, and don't really know VBA at all... I'm sure there are plenty of mistakes with my syntax, since I can't compile this.
Any help would be appreciated.
Thank you in advance

Please Login or Register  to view this content.

[royUK]

Your code is testing for a string, not a formula result. Why have you got this in the cell - ">=" instead of 95%?

Attach an example workbook

[dantonic]

Your code is testing for a string, not a formula result. Why have you got this in the cell - ">=" instead of 95%?

Attach an example workbook

Well the cell with >= XX% is the cell displaying the passing limit... these limits can change. I just wanted to somehow retrieve that limit from that string, because that is what the user will change if necessary.

THank you

[royUK]

Can you justy put the number in?

[dantonic]

Can you justy put the number in?

Well, I'd rather not, I would like to keep the current format, because it is what everyone is used to.

When using conditional formatting I am able to get the values using this for example: =$D$8<VALUE(RIGHT($B$8,SEARCH(" ",$B$8)))
THen I can use those to make my comparisons, and am able to make the cells change color.
The only problem is that conditional formatting limits me to 3 conditions, and I need 4...

Thanks for any help you can offer.

[dantonic]

Any help with this anyone?
Thank you

[MarvinP]

Hey dantonic,

To even start this I'd need a sample file to understand what you are trying to accomplish. The task of "Read my code and make it work!" is way to vague to deal with.

To attach a sample file..... Click on "Go Advanced" and then on the Paper Clip Icon above the advanced message area.

[Leith Ross]

Hello dantonic,

Welcome to the Forum!

I made a few changes to your code. When uisng either a Worksheet_Change or Worksheets_SelectionChange event, it is important to disable events to prevent the event from re-triggering when a cell is modified or a new cell is selected.

It is also important to trap errors whenever the events have been disabled. Once the error is trapped, the events can be re-enabled. Not trapping the error can leave the events disabled and the macro will not work. There will probably be other changes needed, but this should be a good starting point.

Please Login or Register  to view this content.

[dantonic]

Hey dantonic,

To even start this I'd need a sample file to understand what you are trying to accomplish. The task of "Read my code and make it work!" is way to vague to deal with.

To attach a sample file..... Click on "Go Advanced" and then on the Paper Clip Icon above the advanced message area.

Thanks, I apologize, I should have included a file from the beginning. here it is

I actually made some changes and got the code to actually work.
I took my own advice and setup a separate column which I will then hide, to do away with the =VALUE(RIGHT()) function all together, since I just could not get it to work... I set the percentage value in that column, and then simply used that value in my code. Not sure if this was the best solution but it works.

There are still a few issues I would like to take care of.
1. there are some cells in the Range that I would like to keep unaffected or return No color on the background at all times. they are just blank cells. I tried adding a line to check for a "null" adjacent cell, but it's not working.
2. I would like this macro to apply to the cells in real time. As of now, I have to click on the cell and then press enter to apply the changes.
Here is the code:
I don't know why this works vs. the other one. how does the naming affect things? ie Private Sub Wokrsheet_Change(ByVal Target As Range) vs just Sub Worksheet_Change() and setting the Target variable within the function. I also don't know what "ByVal" does

Please Login or Register  to view this content.

[MarvinP]

Hi DanTonic,
I have put a lot of comments in your code and sending you back the file.
Read http://msdn.microsoft.com/en-us/libr...30(VS.71).aspx
Read http://www.cpearson.com/excel/codemods.htm
For answers to your above questions.

I've put a comment in your code on how to jump the code.

[dantonic]

Hi DanTonic,
I have put a lot of comments in your code and sending you back the file.
Read http://msdn.microsoft.com/en-us/libr...30(VS.71).aspx
Read http://www.cpearson.com/excel/codemods.htm
For answers to your above questions.

I've put a comment in your code on how to jump the code.

Hey MarvinP thanks a lot, that helps to understand some things.
However regarding the line

Please Login or Register  to view this content.

The code seems to only execute when a "change" is made in that particular cell of the worksheet, and not anywhere in the worksheet. The changes will only occur in the Month worksheets, and any time something is changed and the "Presentation" worksheet is affected, I would like those those cells to update automatically.

Also I noticed something wrong... apparently "limit" keeps getting set as 1 no matter what. I was trying to debug by adding code to display the value of limit in other cells. you can see what I mean in the file. I even tried changing the cell format to "general" from percentage and declaring "limit" as a float instead of an integer, but to no avail
Any idea why limit not changing accordingly?

Thank you very much

[Leith Ross]

Hello dantonic,

The attached workbook contains the macros shown below. Whenever a cell is changed that results in a formula being recalculated, the conditional format for the YTD column "H" of the appropriate worksheet will be updated. You will need to add the Worksheet_Calculate() macro to each presentation sheet in the workbook.

YTD Conditional Formatting Macro

Please Login or Register  to view this content.

Worksheet Calculate Macro
Copy and add this macro as is to each new presentation sheet.

Please Login or Register  to view this content.

[dantonic]

Hello dantonic,

The attached workbook contains ....

Wow very cool, seems to work well, only thing it turns the "YTD Status" Cell that is supposed to be grayed out green. I think that's an easy fix, I'll spend some time on it tomorrow to try to take care of that.

Thank you very much for the help, I really appreciate it! I guess it helps when you actually understand the language :P

[dantonic]

All right, I've almost got it, there is just one more issue that I am unable to resolve.
Apparently when comparing values, the code is telling it to look at non rounded values.

So for example you can see in the file the Percentage value for metric 5 under the YTD worksheet is 79.76%
This rounds to 80% on the June Presentation worksheet. However the color of the box is still red, even though 80% meets the metric. Even if I change the cell format in the YTD worksheet to round to no decimals, I still get the same issue.

I tried to use the round function in the VB code but to no avail. I also tried setting some more variables as single to simplify the code, but I stopped because I keep getting a "Type Mismatch" error.

Any ideas?

Thanks

[Leith Ross]

Hello dantonic,

It is an easy fix. The variable limit is declared as an Integer. Declare it as a Double and it should work just fine.

[dantonic]

Hello dantonic,

It is an easy fix. The variable limit is declared as an Integer. Declare it as a Double and it should work just fine.

well actually in the most recent copy of the file it is declared as single, however that is not what is causing the Type mismatch, it is the other variables. Note, those variables are commented out in this code, just so it wouldn't give the error.

My main issue is that I need a way for the target cell to be evaluated rounded to no decimals on the percentage values.

Please Login or Register  to view this content.

[Leith Ross]

Hello dantonic,

To prevent rounding in VBA use the Fix function. This removes the decimal portion of number without rounding, at least in the positive range of numbers.

Please Login or Register  to view this content.

[Leith Ross]

Hello dantonic,

To prevent rounding in VBA use the Fix function. This removes the decimal portion of number without rounding, at least in the positive range of numbers.

Please Login or Register  to view this content.