15 conditional formats?

bradnailer

New Member
Joined
Jul 27, 2007
Messages
26
Conditional format has only 3 criteria. What I wanted to do is search for a string of text in the cells in column A and if its there, color all the text in the cell <specific color>, bold it & finally increase the size to 12.

I used this in the conditional formatting:

=ISNUMBER(SEARCH("Apple*",A1)) 'blue, bold, 12 font

=ISNUMBER(SEARCH("Berry*",A1)) 'red, bold, 12 font

=ISNUMBER(SEARCH("Egg*",A1)) 'green, bold, 12 font

but I have 12 more to conditional format.

Is someone can show me how to start and finish the macro, I can fill in all the middle info!

Thanks
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
=ISNUMBER(SEARCH(A1,"apple,berry,egg,"))

is only one condition and the second argument can be made longer.
 
Upvote 0
now that I read my post again, more specifically I was going to use 5 or 6 colors for the different text. I'll update my original post...
 
Upvote 0
Ok, I have it working (it doesn't appear I need to specify column A) but its only changing text I type in, not any existing text thats already in the file.

I created a new module in personal.xls and copied the code into it but I can't see it in the Alt+F8 macro list after I exited and saved then reopened it.

Any ideas?

I removed the word "Private" from "Private Sub" thinking that may make is usable by all files?? no go. I can still type new text in the file and it formats it though...unfortunately, I import everything.
 
Upvote 0
Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit

Sub Plant_formatting(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case "*Plant:"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = False
Case "apple:"
Cell.Interior.ColorIndex = 4
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

This code from the website
 
Upvote 0
Try this code.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LstRow As Integer
Dim Rng1 As Range
'   Only look at single cell changes
    If Target.Count > 1 Then Exit Sub
'   Set Target Range
    LstRow = Range("A65536").End(xlUp).Row
    Set Rng1 = Range("A1:A" & LstRow)
'   Only look at that range
    If Intersect(Target, Rng1) Is Nothing Then Exit Sub
    On Error Resume Next
    For Each Cell In Rng1
        If InStr(Target.Value, "Apple") Then
            Target.Font.ColorIndex = 5
            Target.Font.Bold = True
        End If
        If InStr(Target.Value, "Berry") Then
            Target.Font.ColorIndex = 3
            Target.Font.Bold = True
        End If
        If InStr(Target.Value, "Plant") Then
            Target.Font.ColorIndex = 10
            Target.Font.Bold = True
        End If
    Next
End Sub
Be sure to place this code in the WorkSheet Module.

Right click sheet tab
Click "View Code"
Paste the code into the panel that opens. ("WorkSheet" Module)
Alt-Q to quit the VBA Editor.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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