Multiple Data Bars

sherman51,
Your title do show the issue you are running into, please update it
Attach a sample of your data to prepare the macro

These settings work for me. Do you want VBA code?

FormattingRule.jpg


NegValues.jpg

Paul - that is exactly what it does.
Put 50 in the cell and it goes green, put -50 in the cell and it goes red
Did you make the minimum -100?

That is not a problem

Let's say the cell to be formatted (say E1) gets its value from A1 and that A1 contains values between -100 and +100

The formula in cell E1 :

Formula:

Please Login or Register  to view this content.

This turns -32 into -68 etc and should give you what you want

Under the formatting rule, uncheck the box indicated in picture

FormattingRuleShowBarOnly.jpg

VBA does indeed seem the only way
Try the attached workbook.
Amend value in A1 and see if what you get in E1 is what you want

this is the formula in cell E1 - turns negatives to positives

Formula:

Please Login or Register  to view this content.

The code is on the sheet's module

Please Login or Register  to view this content.

Please Login or Register  to view this content.

IF this is what you want I will modify the code so that it is available in a more general way (ie using variables, and loops), instead of only one cell at a time

Hi Paul

Here is a more general solution for you and now uses selection.FormatConditions(1).formula = “=if(c3>59, true, false)” (I was distracted down the change "negative" to "positive" and forgot to go back to the beginning!)

In the test worksheet, the range is set to E1 to E50 and so you will need to amend that to suit yourself.
It runs automatically when anything changes in column E. But you could run it anytime

The cell bar is coloured red (if negative) or green (if positive), and the bar will totally fill the cell if value = 100 or -100

If you need any help adapting it to your workbook, give us a shout.
Otherwise if this gives you everything you need, please mark the thread as solved (under thread tools at top of thread)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

That works great Kevin, thank you very much ...

With regards to applying it to my workbook, i could do with a bit of advice, i will want to apply it to 26 columns but alternate cells ie, L4, L6, L8 up to L68, then N4, N6. N8 up to N68 ... and so on ..

also, would it be possible to have solid fill rather than the graduated ends ... no big deal if not

Thanks again
Paul

One other issue I just discovered Kevin, the cells to be formatted will get their value from a calc that comes from a live feed and may fluctuate meaning that at first the value may be positive but in time it may change to negative. I have found that when i tried this the first value would work and the correct data bar would appear, but if it was positive and the incoming data ment that it changed to negative, tha calc changed to show the correct value but the data bar dissapeared leaving a clear cell with just the number.

sorry to be a pain but we are so close ...

I found a way to make the bars solid btw, so thats ok

Cheers
Paul

"would it be possible to have solid fill rather than the graduated ends"

Add this line in both the "red" and "green" zone

Please Login or Register  to view this content.

1. Are the 26 columns adjacent to each other?
- makes it easier if they are

2. Are all the odd rows blank (spacers between all the green & red) ?
- if blank then we can format them as well as it will not show

I had not noticed the edit to your last post.
I am not concerned about the formatting disappearing - it appears to be an Excel quirk. I've noticed it myself too - it's temporary until the macro is re-run. Just call the macro at the end of any procedure where the values are being updated. Notation is

Please Login or Register  to view this content.

Are all values updated automatically, rather than direct input?

I will sleep on the " non-adjacent cells that require formatting " puzzle.
- hopefully we can come up with a "rule" that can be applied (I would not like to list all those cells individually)
- the rows are easy - all the even numbers
- I am guessing that columns will need listing separately or is there a pattern?
- there are 26 columns being formatted, how many other columns are likely to be embedded amongst our 26?
- Will there be other conditional formatting in the other columns?

"I will want to apply it to 26 columns but alternate cells ie, L4, L6, L8 up to L68, then N4, N6. N8 up to N68 ... and so on .."

which means every other row and every other column starting at L4, in which case this will work:
(see attached workbook, sheet2)

Please Login or Register  to view this content.

Are we there?

Thanks again Kevin ..

I am not sure where to put the "call Macroname"

RE the cells to be formatted, i am quite prepared to put in the legwork to make named ranges of all the cells required if named ranges can be used, I realised last night that when i said 26 cols it is in fact 52 cols, its every other (even) cell decending starting with 4 up to and including 68 in each case ... so L4 L6 .... L68, but the cols are not evenly spaced, they start with L & N, then t & V, then AB & AD etc ...

The sheet is being updated every 10 seconds (with vba) and is linked to another sheet, thats how the incoming data arrives ..

Cheers
Paul

Did post 19 not give you exactly that?

Where is the VBA sitting that updates the worksbook - is it in this workbook or the other one?

I am offline now for about 3 hours - car MOT

Post 19 formats every other column, I would need the following pattern, starting with L4 - miss 1 col - N4, then miss 5 cols - T4 miss 1 col - V4, then miss 5 cols - AB4 - miss 1 col - AD4 .. and so on, ...

Every other row is correct .. so 4, 6, 8, 10 up to 68 ..

The calc update every 10secs vba is in another file that I open first ..

Cheers
Paul

I offer you 2 choices:
1 Put a marker in each column that should be formatted in this way ( eg an "X" in row 100)
- this is easy and flexible - would allow you add extra columns and it would still work without modification
- VBA would loop through looking for the marker
OR
2 Hard wire the columns
- means inputting each column number/letter

Which do you prefer?
(my choice would be No1 )
Let me know and I will tailor the code.

It will be marginally slower than reading directly from hard code, but not a choker.
I do not think I would hook it to a "change event" routine. Either run it automatically at end of other procedures or as once-off if you amend anything manually.
I would insert the macro at sheet level to avoid having to refer to the worksheet by name. Everything happens within the same sheet.

See sheet3 in attached workbook. Looks ok to me.

Code included to dump a message to screen in case rows are later added/deleted above row 100) - the value in cell I100 also acts as a reminder explaining the "X"

Please Login or Register  to view this content.

PS
Just a thought - it may be helpful to remove conditional formatting from a column (if you put an X in the wrong place etc or change your mind) - so I will send you something to do that later.

All that is missing is a trigger to fire the macro.
The formatting is correct if you run the macro manually after putting X in the column or after putting in a negative value

If you add this macro at sheet level then it will run the macro with every change you make. Make sure amend "J:CZ" to include all your columns
TwoColourDataBar is the name of the macro

Please Login or Register  to view this content.

Thanks Kevin, i have inserted the trigger code after the code in the sheet, i am novice so not sure if that was correct, i also deleted the test data from teh code, again I don't know if that was the right thing to do but it got a result. I now can change from pos to neg and back ..

so now when i change a number you see each cell highlighted and then it stops at the last cell, but if that happend it would be continually dumping me at the end of the sheet. is it possible that when the figs update that the sheet stays static? because i am usually studying the sheet and it would be unworkable to be put at the end of the sheet every 10 secs ..

Also, i still get no joy when i enter in a fresh col that I have marked with an x at row 100 .. Sorted!! I just realised it needs to be a capital ...

I now have to leave for the evening so will be in touch tomorrow
Thanks
Paul

Hi Paul

Test is for upper case "X"

so either input in Upper Case
OR
- amend the code to include lower case "x"

Please Login or Register  to view this content.

OR
- amend the left side so that it does not matter what you input

Please Login or Register  to view this content.

The mod you asked for:
This leaves cursor where it is and by wrapping "ScreenUpdating" False/True around things stops the annoying screen flicker.

Please Login or Register  to view this content.

1. Enter "GreenRed" in cell "I100" in each new worksheet or remove ths bit of code
(it is only there to help you later if you inset/delete rows - the conditional formatting is dependant on there being an "X" in row 100 - if you insert/delete rows then the "X" will end up in a different row)

Please Login or Register  to view this content.

2. The random formatting is the legacy of previous testing left behind. Unfortunately it is invisible until you put a value in the cell!

Here are 3 macros to clear the debris. Suggest you attach a shortcut to each one for ease

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

RunningMacrosWithShortcuts.jpg

The formatting should clear instantly - whether cells populated or not.
(EDIT - The formatting should clear instantly when the macro is next run)

Any formatting legacy is in the worksheet not in the vba.
So if you copy (or template) one worksheet to create another, the new sheet takes the old sheet's formatting at the point of copying

This clears conditional formatting from all cells in the active worksheet

Please Login or Register  to view this content.

If you run that it clears everything, and then you can run the other macro which will then only format the cells you want.
(NB if you have conditionally formatted any other cells those will need re-formatting separately)

EDIT
and to answer your question
"but if i copied (re-typed?) your vba into a new sheet with a new name would that be like a fresh start without the legacy ?"
Yes, provided you have created a fresh sheet

You are welcome. I hope all your bars are green!