LinkBack URL
About LinkBacks
Hi, I want to create a function or vba code for a column IF any text in that column is = to Selfpay turn them all to Red text. I want to do this without using conditional formula. Thanks
Hi, I want to create a function or vba code for a column IF any text in that column is = to Selfpay turn them all to Red text. I want to do this without using conditional formula. Thanks
What does "them all" mean? All cells with that data value or the whole column?
I would like to have a range like K5:K70.
Adjust the range to the column and cell range you want "watched", then put this macro into the sheet module:
Please Login or Register to view this content.
Last edited by JBeaucaire; 03-10-2009 at 02:20 PM. Reason: Fixed color to change to RED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
HTHPlease Login or Register to view this content.
Or, if you only want it to happen when you first enter SELFPAY into the cells, and not check the cells all the time (meaning there are no formulas changing those values), then use this instead:
This occurs in real time but only when you type in those cells.Please Login or Register to view this content.
The previous macro I provided occurs in real time and will update those cell color even if formulas are possible changing their value.
If you want "live conditional formatting" (you should probably use conditional formatting - but let's ignore that for now) then you can use a worksheet_change event macro but remember this will remove the undo functionality for that sheet . If you do want this, I would recommend a macro which actively finds each instance of "selfpay" then formats the range, instead of a macro which searches every single cell of the range by default - macros which operate on _change should be very "light".
CC
Hi the code works great, it changed the color when I run the macro. It there a way that this code will automatically run in the background? let's say I enter "selfpay" in column K it will automatically change the color without me running the macro? Thanks
HTHPlease Login or Register to view this content.
Last edited by Cheeky Charlie; 03-10-2009 at 02:40 PM. Reason: hot to change font colour application area
This code changed all the cloum in K to Red, not just the selfpay?
Originally Posted by Cheeky Charlie
HTHPlease Login or Register to view this content.
Perhaps you could be more clearIF any text in that column is = to Selfpay turn them all to Red text.
this certainly didn't help:
as it implies the whole column within a given tableI would like to have a range like K5:K70
DonePlease Login or Register to view this content.
/thread
Last edited by Cheeky Charlie; 03-10-2009 at 02:46 PM. Reason: wrong error check!
I am sorry if I did not explain clear enough, I want a vba code that will change the color of the text or the field to red if "Selfpay" is located under column K (let's just assume the range is K5:K70).
JBeaucaire's Macro works great, but is there anyway I can make it to work without running the marco? (Column K is a list box, assume if I select Selfpay it will automatically change the color?)
Cheeky Charlie, I tried your code but it doesn't do anything, maybe I am doing something wrong where should I put the code under? or module? I am a novice excel user...
Update 1 (I put the code under the sheet and it works PERFECTLY) Thanks alot Cheeky
UPdate 2 (If it was Selfpay and I select another option, it remains red) let's say if it was selfpay (red) and I change it to something else it should turn black.
Last edited by jimjaix; 03-10-2009 at 02:59 PM.
My original, like JB's was based on a one-time run
This should now resetPlease Login or Register to view this content.
Mine is not a manual macro. You should not be running it manually. Right-click on the sheet-tab name where this is supposed to run and VIEW CODE...paste the macro in there.
Mine were designed to operate on their own if you put them in the SHEET module.
Yup it does works now, thanks a lot Cheeky
Sorry JBeaucaire, I was putting the code under module that was why it didn't work, I tried putting on the Sheet code selection and it works like a charm. Thank you so muchMy original, like JB's was based on a one-time run
This should now resetPlease Login or Register to view this content.
oops - one was on calculate, t'other on change - why the difference?
The Calculate version is in case there are formulas in column K making changes to the pay selection. If some other cell changes a value and THAT changes the column K value, it won't change the color unless it's a Worksheet_Calculate event.
The WorkSheet_Change version only works when manual changes are being made IN column K. Significant difference.
JBeaucaire & Cheeky Charlie I have something that's similar to this, instead of looking for the word “Selfpay” I want to do a date comparison. I have a list of date in a column I; I want the data to be in red text if it’s greater than today(). How do I implement something like this?
Use conditional formatting...
Any ways to do it without using conditinoal formula?
Why don't you want to use conditional formatting?
just don't want to lol... I want to learn more about coding and stuff
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks