Change cell color w/o using conditional formula

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.

Sub FindSelfpays()

Dim rRange As Range
Dim rCell As Range
Dim sFirstAdd As String

Set rRange = Range("A5:M70") 'Change this to suit your needs

On Error Resume Next
    Set rCell = rRange.Find("Selfpay")
    If Err.Number <> 0 Then Exit Sub
On Error GoTo 0

sFirstAdd = rCell.Address
Do
    Range(Cells(5, rCell.Column), Cells(70, rCell.Column)).Font.ColorIndex = 3
    Set rCell = rRange.Find("Selfpay", rCell)
Loop Until rCell.Address = sFirstAdd

Set rCell = Nothing
Set rRange = Nothing

End Sub

HTH

This code changed all the cloum in K to Red, not just the selfpay?

Originally Posted by Cheeky Charlie

Sub FindSelfpays()

Dim rRange As Range
Dim rCell As Range
Dim sFirstAdd As String

Set rRange = Range("A5:M70") 'Change this to suit your needs

On Error Resume Next
    Set rCell = rRange.Find("Selfpay")
    If Err.Number <> 0 Then Exit Sub
On Error GoTo 0

sFirstAdd = rCell.Address
Do
    Range(Cells(5, rCell.Column), Cells(70, rCell.Column)).Font.ColorIndex = 3
    Set rCell = rRange.Find("Selfpay", rCell)
Loop Until rCell.Address = sFirstAdd

Set rCell = Nothing
Set rRange = Nothing

End Sub

HTH

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

IF any text in that column is = to Selfpay turn them all to Red text.

Perhaps you could be more clear

this certainly didn't help:

I would like to have a range like K5:K70

as it implies the whole column within a given table

My original, like JB's was based on a one-time run

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rRange As Range
Dim rCell As Range
Dim sFirstAdd As String

Set rRange = Range("A5:M70") 'Change this to suit your needs
If Intersect(Target, rRange) Is Nothing Then Exit Sub

On Error Resume Next
    Set rCell = Intersect(Target, rRange).Find("Selfpay")
    If rcell is nothing Then
        intersect(target,rrange).font.colorindex = 1
        Exit Sub
    end if
On Error GoTo 0

sFirstAdd = rCell.Address
Do
    rCell.Font.ColorIndex = 3
    Set rCell = Intersect(Target, rRange).Find("Selfpay", rCell)
Loop Until rCell.Address = sFirstAdd

Set rCell = Nothing
Set rRange = Nothing

End Sub

This should now reset

Yup it does works now, thanks a lot Cheeky

Originally Posted by Cheeky Charlie

My original, like JB's was based on a one-time run

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rRange As Range
Dim rCell As Range
Dim sFirstAdd As String

Set rRange = Range("A5:M70") 'Change this to suit your needs
If Intersect(Target, rRange) Is Nothing Then Exit Sub

On Error Resume Next
    Set rCell = Intersect(Target, rRange).Find("Selfpay")
    If rcell is nothing Then
        intersect(target,rrange).font.colorindex = 1
        Exit Sub
    end if
On Error GoTo 0

sFirstAdd = rCell.Address
Do
    rCell.Font.ColorIndex = 3
    Set rCell = Intersect(Target, rRange).Find("Selfpay", rCell)
Loop Until rCell.Address = sFirstAdd

Set rCell = Nothing
Set rRange = Nothing

End Sub

This should now reset

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 much

Originally Posted by JBeaucaire

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.

oops - one was on calculate, t'other on change - why the 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