Changing cell background color/borders function

Hello, please keep it simple, i am a complete noob

i would like to have a function that works something like this


{name of the function} ( {adress of a cell containing a color i want to use} ) {cells that will be colored}

simply put, the function copies a backround color of one cell to selected cells no matter what the values of those cells are

i would use this function to see the shift of different people (colors) on a time table.
i could use for example an IF function to color a certain range when i write pick a 1 hour shift, and i could color a bigger range when i pick a 2 hour shift


It seems that changing the backround color is complicated but it would work if i could at least change the bordest with the same logic.. something like

{name of the function} ( {adress of a cell containing a color i want to use} {adress containing thickness of borders ) {cells that will be colored}


-------------------------------------------------------------------------

here is what i have got so far


This changes the color of a cell defined by user - but only to a color predefined in the code, all i would need (i think) is to somehow be able to define that color by a cell (either color of the cell or numbers, i could make it work but id prefer the color directly to be read from a pre-colored cell > it should be possible to change and update all the colors)

-----------------------------
Function SetIt(RefCell)

RefCell.Parent.Evaluate "getRGB(" & RefCell.Address(False, False) & ")"
SetIt = ""
End Function

Sub getRGB(RefCell As Range)
RefCell.Interior.Color = 123456 ***this is the color number, i would like to be able to change
End Sub

-----------------------------

I found this online and it works very well but i cant define the color - i tried putting the numers of rgb there as variables R G and B, that didnt work, i dont really have any idea how to put variables in there.. "noob" id rpefer it to read the color directly anyway..
i also found a piece of code that takes a cells color and writed down its RGB value, but i cant put them together due to lack of my skill

Thanks in advance for the help

Hi there,

Sorry, but the ONLY thing that a User-defined function can change is the VALUE displayed in the cell where the UDF is entered.

If you want to change anything else, you'll have to use a subroutine.

Hope this helps.

Regards,

Greg M

But this function i provided succesfully changes a color, i just want to define which color

i gave it a quick test and it works great!

i couldnt understand the syntax to be able to do it myself but i knew you could define the color somehow!

ive seen a lot of people ask for this function too as i was looking for solutions but they mostly gave up on it (too soon!)


ONE more addition, if possible, could you make it ctrl+alt+F9 itself somehow, so it works dynamically when i change the source color? or maybe there are better solutions too, im definately going to use this often, just gonna change the functions name, i prefer shorter names

i tried it out on droplists as well and with text and different styles, it doesnt seem to glitch outside of what i mentioned, it also updates the color once you retype something inside any of the target cells, maybe someone would make a use of that

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated

Regarding:

ONE more addition, if possible, could you make it ctrl+alt+F9 itself somehow, so it works dynamically when i change the source color?

I don't think this is possible. Even making the function Volatile (never a good idea anyway!) would only cause it to recalculate on the basis of its argument - as the argument is a Cell (not a cell background colour), no recalculation would be triggered when the source cell background colour changes.

The best I can suggest is to run the following routine whenever you change the background colour of one or more source cells:

Please Login or Register  to view this content.

You can change the highlighted value to suit your newly-renamed Function.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

So this formula checks cells with the background function for changes? where should i put it, in the sheet or in the module or perhaps in the background functions module?

WAIT, i used the background function the way i planned to and it works very well, the colors update due to the IF function i use it with.
or perhaps i forgot to delete the mod you just gave me XD

i want you to také a look, its the last sheet called sheet2, this is my random file so theres a lot of unnecesary mess, sorry for that, there are also two sheets with this name but youll figure

it shows a time Schedule where you choose name and time, it then colors the time accordingly - next to it there is an orange box showing the IF code i made, také a look.
Is there an easier way to make this work? the code seems to recopyable but i feel like it is not very good, since eventually there are gonna be more names (at least 7) and i have to type each variability each time XD

also it has one buug id love you to help me solve

when you choose 1,5 hours it colors the right cells, but when you then decide to choose 0,5 instead, it doesnt delete them anymore

i was thinking deleting them each time, but then if there was a time assigned in the next hour it would delete that too - well it is fine since its go back after refreshing, but the other color still remains

for example, i chose orange for 0,5 in the third column, then i chose 2 hours in green in the first column "accidentally" then i changed back it to 0,5

now everyone would think theres no free space, take a look and thank you for the help!

(my attempt of solving it is in the yellow cell, it works but it would delete the whole thing each time and that is unconvenient)

Errmmm ... I think you forgot to attach the workbook!

Not again xD, i keep forgetting to press the upload button

ive been playing with it and i rememebr trying a different if false function at the end of the two lines.. the upper one stays the same it is if cleared, the lower one deletes itself - and also others if they were in range, which is the reason i cant use that

i figured simply selecting the whole thing, clearing the color to no fill and then pressing the refresh button works the best, how would the line for "replace selected area with no fill" be, i could just add something like it to the refresh button

another bug found - the function doesnt work when the target and source cells are from different sheets
not sure why, it is possible its because of the workaround

Hi again,

Sorry, but as far as your Post #8 is concerned, I really can't understand what's supposed to be happening

Regards,

Greg M

Hi again,

-tried to add your changes to the code - didnt work
copied the whole code - worked (lol not sure why, it looked the same)

It's very definitely NOT the same! The latest version includes the name of the worksheet which contains the source colour cell.

The attached workbook appears to work correctly in that cell colours on Sheet1 are copied to the appropriate cells on Sheet2.


I haven't time to look at the remaining points in your last post but I'll see what I can do later on.


Regards,

Greg M

also the coloring is only in the first cell, i plan on copying it later but i feel like the slow computer at work already has trouble with it, hopefully some autosave function will save it every three minutes lol

Hi again,

Using the function on different worksheets definitely makes life more complicated!

The following version of the function seems to work in the attached workbook:

Please Login or Register  to view this content.

Regards,

Greg M

a quick press of ctrl alt and F9 in the file you added proved that to be wrong

Nope, false alert, still crashes, less often but it still does.

I really wanna figure out why, could it be because of this fuunction? after all its in about 150 cells. or maybe the checkboxes but i havent found anything better than those...

its the last thing keeping me from using it

Hi again,

I haven't found any situation which causes the workbook to crash, but perhaps I'm not experimenting with it as much as you are.


Regarding:

BTW take a look at the very right, have you seen the IF function using this? it works but i feel like it could be done better LOL

I'm sorry, but I wouldn't even look at a formula that contains 32 nested IF's

Regards,

Greg M

haha, i need to find a way to replace it with some easier formula, havent found it yet, also ever since the update of your formula it crashes less, ill see what i can do once i find what causes it.