replace part of formula in conditional formatting

[Eville]

Hi all,

I have a row with 220 conditional formatted cells. each cell has its own unique formula (cell value is, not between, =$B$21<1671, and, =$B$21>1660, conditional formatted is a right border). every cell to the right the numbers change with 10 down so the above formula is of I29 and J29 looks like this: (cell value is, not between, =$B$21<1661, and, =$B$21>1650, conditional formatted is a right border).
Once all 220 cells are formatted i dragged it down for 9 more rows. Then 1 empty row then i start all over again BUT the first part of the formula changes from =$B$21 to =$B$31.
I have to change this formula another 1100 times and its driving me nuts lol.

My question is: Is it possible with conditional formatted cells with border as format to exchange a part of the formulas (the reference cell B21, B31 etc) without loosing the format?
either with a macro, a vba or a user form ... i tried 2 record a macro but that replaces the whole formula because i cant get the macro 2 replace a part of the formula. i found a vba but that only works with 'Formula if' and not 'cell value is' ><

Thx loads in advance!

Ev

[Bob Phillips]

Be smarter with the formulae

=$B$21<(1660+COLUMN(A12)*10) and =$B$21>(1650+COLUMN(A1)*10)

I don't get the second bit.

[Eville]

Be smarter with the formulae

=$B$21<(1660+COLUMN(A12)*10) and =$B$21>(1650+COLUMN(A1)*10)

I don't get the second bit.

in cell B21 a value is inserted varieting from 1670 till 0. this number cant be generated but has 2 be entered by hand >< i know its a pain, should be forbidden 2 enter data by manually :P

[Bob Phillips]

I am saying you don't have to re-enter every formula, you calculate the test value.

[Eville]

I am saying you don't have to re-enter every formula, you calculate the test value.

Ok thx for the advice i give it a go

Ev

[Eville]

cant get the formula to work. ah well guess i find 2 find me mp3's on pc again and do few hrs copy & paste lol
Thx for the advice anyway m8

Ev

[Bob Phillips]

Post your workbook and let us try.

[Eville]

grrrr i thought i was doing well untill i hit the key 'clear logbook' lol ><
It copy & pastes info from the 1st block doh, recorded a macro with right click 'clear contents' but i get a run time error.
thought 2 be smart and make an empty sheet that i can hide & use as reference to clear a new sheet but its doing me head in lol.
looks like i have 2 start from scratch again with making a new sheet and clearing it. Ill post what i have now but still need 2 do alot of work ><
PS: info in cell A1 is in dutch, its about whats changed since the version we r using atm.
Most is improved but clearsheet, copysheet & the width formula r a pain atm lol

Ev

Thx in advance again, afk 4 dinner now

[Bob Phillips]

This is what I mean

Select cells I9:IH17 on Logsheet

Goto Cells>Conditional Formatting

Change the Condition 1 dropdown to Formula Is

Add this formula

=AND(INDEX($B:$B,INT((ROW()+1)/10)*10+1)>(1670-(COLUMN(A$1)*10)),INDEX($B:$B,INT((ROW()+1)/10)*10+1)<=(1680-(COLUMN(A$1)*10)))

Set you desired format

OK out

You can then just copy I9:IH17 and paste into I19 etc.

Much simpler.

[Eville]

Hi xId,

I tried the formula (i cant understand it btw ><) and comes up with an error highlighting this part: $B,INT
Thats also my prob, i cant read the formula so when it goes wrong i cant correct it lol *hides* :P
Hope u have a clue what goes wrong?
Also when i use this formula (once its working) i think i can use the empty sheet macro then write another macro after it to fill the other blocks again?
Thx in advance again

Ev

[Bob Phillips]

Beig Dutch, you probably need

=AND(INDEX($B:$B;INT((ROW()+1)/10)*10+1)>(1670-(COLUMN(A$1)*10));INDEX($B:$B;INT((ROW()+1)/10)*10+1)<=(1680-(COLUMN(A$1)*10)))

or even

=EN(INDEX($B:$B;INTEGER((RIJ()+1)/10)*10+1)>(1670-(KOLOM(A$1)*10));INDEX($B:$B;INTEGER((RIJ()+1)/10)*10+1)<=(1680-(KOLOM(A$1)*10)))

[Eville]

Beig Dutch, you probably need

=AND(INDEX($B:$B;INT((ROW()+1)/10)*10+1)>(1670-(COLUMN(A$1)*10));INDEX($B:$B;INT((ROW()+1)/10)*10+1)<=(1680-(COLUMN(A$1)*10)))

Just got home from work >< and was curious whats posted lol
This code works fine but ... (always a but and thats the worst part lol) the section i want the borders to be displayed into goes decreasing from 800 ... 700 then 400 (doesnt need to go to 0 all the way) and then again from 0 to 400 then 700 ... 800 etc increasing.
So the formula can stop at 610 width at cell DK9: DK17. Then at DL9: DL17 the width is 400 (we aint going any smaller) then at EF9:EF17 starting again at 400 and EG9:EG17 width 610 and so on in reverse way from the other side.
Think u can imagine its visualisation of the width of a production roll based on center of the material were center is 0 and the edges are the max width.

Im sorry if im a pain in the butt lol. For you it might be possible to write a code for it that works but for me the conditional formatting as i did it in the attached file was my best way of doing it
Hope u can still help find the code working the same way as my conditional format cell value is ... not between ... :o))

Thx in advance again!

Ev

[Bob Phillips]

I thought I saw that but when I looked at the cells there were some that you hadn't applied CF to. You also say decreasing from 800 whereas I saw it decreasing from 1670. And why would it stop at 610, and start at 400 at DL?

Are you saying it ges down then up?

[Eville]

I thought I saw that but when I looked at the cells there were some that you hadn't applied CF to. You also say decreasing from 800 whereas I saw it decreasing from 1670. And why would it stop at 610, and start at 400 at DL?

Are you saying it ges down then up?

Hi xld
Yes it starts from 1670 decreasing all the way to 610 but basically the smallest width we have is 700 (maybe in future 650 so wanted to add that bit) and then a special size is 400 so had to add that as well.
so the code till width 650 is fine. from 650 it needs to 'jump' to 400 at cell DL... then from DL... till EF... its empty (doesnt have 2 be CF)
From EF... till end (IH...) it should work same as your 1st code but reversed.
I added a screenshot so maybe that explains abit more what i want
Hope this description & screeny helps you.
Thx loads again, still surprised you didnt give up yet

Ev

[Bob Phillips]

If it were me, because the data is so variable, I would create a table on another sheet, in rows, and refer the CF to that. The table would be easily changeable would give you total flexibility.

How about that?

[Eville]

If it were me, because the data is so variable, I would create a table on another sheet, in rows, and refer the CF to that. The table would be easily changeable would give you total flexibility.

How about that?

Was already afraid it wouldnt be so easy 2 make one single formula for all the different cells ><
Atm i have LOGSHEET and LOGSHEET(1). the 1st logsheet i wanna use as blank logsheet and hide it but use it to make new fresh logsheets.
I remove the clear logsheet button so the CF stays the same and every new logsheet(2), (3) etc will be a simple copy from the hidden LOGSHEET tab. I think its possible and easiest sollution for me. Only need 2 edit the last 3 coils, means im over half way lol :P
Im not gonna choose for the table option because i only have limited time atm and want 2 have the new sheet up&running asap. I migth ask help to make a new sheet with tables etc like u proposed but atm i stick to me old CF. Its not the best and easiest sollution but it works i hope
Thx loads for the help & efford tho xld
Hope u still wanna help me

Ev

[Bob Phillips]

It will take 10 mins. I will take a crack at it tomorrow morning, and you can the n just tune the table to suit.

[Eville]

It will take 10 mins. I will take a crack at it tomorrow morning, and you can the n just tune the table to suit.

would be great m8, thx loads in advance! but write a little how to with it if its getting complicated

Ev

[Bob Phillips]

Here you are, this is my suggestion. You wil see a new sheet, Lookup, which has all the values and you cann tune these to whatever you may need. The sheet Logsheet(1) has the CF to pick up these values.

Play and let me know.

[Eville]

Here you are, this is my suggestion. You wil see a new sheet, Lookup, which has all the values and you cann tune these to whatever you may need. The sheet Logsheet(1) has the CF to pick up these values.

Play and let me know.

Omg im really impressed, ty vm!
Looks like im gonna have 2 spent my weekend with wife & kids now
Still got a few things on sheet to improve but this was my main pain-in-the-bum thing that needed attention.
Cosmetic things like borders, alignments, font size, bolt or not etc and then protection so ppl cant change it lol. then hide the lookup & empty sheet and think then im done for now.
Thx again, hope i can do this kind of things myself one day but atm i dont even have a clue how it works so im saving it somewere else as well and gonna try a few things with your code to see if i can understand any of it

a very happy Ev