Conditional formatting using VBA

CFG2018

New Member
Joined
Jan 14, 2018
Messages
3
I would like to write a conditional formatting VBA that allows any cell that contains certain words by formatted as well as the cell immediately above it.

So for example


A2: 4
A3: Apples


A4: 5
A5: Oranges


A6: 8
A7: Apples


In this case cells A3 and A7 contain the would ‘Apple” so cells A2, A3, A6 and A7 would all be formatted the same. Cell A5 contains the word Orange so cells A4 and A5 would be formatted differently, etc etc. My eventual list could contain 30 words. Some of the words will require the same formatting but the majority won't. How does one access the correcting coding for the colour spectrum?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Welcome to the Board!

I don't think you will want to use Conditional Formatting, as if you have 30 different words and colors, that means 30 different rules! Unless you have multiple words returning the same color.
You probably want to use Event Procedure VBA code.

The big question is, how is this information being entered into column A?
Do you want code to run as you manually enter the data, or are you wanting to run code on existing data?
 
Upvote 0
I am not familiar with "Event Procedue VBA code". Any help would be appreciated
The entries into the various cells are being generated by a "vlook up" function on a separate sheet.
 
Upvote 0
So, do you just want some VBA code that you can run on demand to do this highlighting?

Here is some code that will do that. You can just keep adding "Cases" to the statement below. In the first one, I showed how to have more than one value return the same color.
To get other colors, you can turn on the Macro Recorder, and record yourself selecting some different colors. They will be assigned numeric codes. You can use these code instead of "vbYellow", "vbRed", etc.
Code:
Sub MyFormatting()

    Dim lastRow As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last populated cell in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows starting from row 2
    For r = 2 To lastRow
        Select Case Cells(r, "A")
            Case "Apple", "Pear"
                Range(Cells(r - 1, "A"), Cells(r, "A")).Interior.Color = vbYellow
            Case "Orange"
                Range(Cells(r - 1, "A"), Cells(r, "A")).Interior.Color = vbRed
            Case "Banana"
                Range(Cells(r - 1, "A"), Cells(r, "A")).Interior.Color = vbBlue
        End Select
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top