Run-time error 13 type mismatch using conditional formatting

Joined
Aug 22, 2007
Messages
4
I am attempting to run a macro that will make conditional formatting I have applied permanent, and then remove the conditional formatting from the cell. My application for this is a master fantasy football cheatsheet, with multiple columns of the same player names ranked in different orders. When a player is drafted I type their name in cell A1, and conditional formatting is setup on all cells with names such that all cells containing that name (presumably once in each column) will be highlighted/bolded/etc. For example, if I typed "LaDainian Tomlinson" in cell A1, I would be able to find his name in each list/column (from the conditional formatting that will make it stand out), and then run the macro to make this formatting permanent (show he has been drafted), and remove the conditional formatting code, leaving the effects. I found a macro online to do this at the following link, but am having some issues:

http://exceltips.vitalnews.com/Pages/T0082_Removing_Conditional_Formats_but_Not_the_Effects.html

When I run the macro on any cells not containing conditional formatting, I get the message: "The formatting based on the conditions in the range (whatever is highlighted) has been made standard for those cells and the conditional formatting has been removed."

This messsage is precisely what I am trying to do, however, when I run the macro on a cell with conditional formatting (when it would actually be beneficial), I get "Run-time error '13' Type Mismatch"

When I go to debug, the error appears to be on the line "If CDbl(rng.Value) = CDbl(FC.Formula1) Then"

I am using Excel 2003, and unsure how to proceed. I would be the Fantasy Football king if I could get this macro to work. Can anyone solve the mystery?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
make conditional formatting I have applied permanent
I have seen somewhere that it is possible in some later versions of Excel to find the conditional format of a cell in code. Don't know if 2003 does it though.

Why not just set up a macro to do what conditional formatting does ?
i.e If "some condition" Then cell interior colorindex = 6 - or whatever.
I do this in Excel 2000 very frequentyl.
 
Upvote 0
re: make conditional formatting I have applied permanent

So how I can modify this code to make several cells turn permanently yellow when the text in $C$1 matches the text in those cells?

Code:
Sub ReplaceByFormat()
Application.FindFormat.Clear
Application.FindFormat.Interior _
.ColorIndex = 6
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior _
.ColorIndex = xlNone

Cells.Replace What:="", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=True, _
ReplaceFormat:=True
End Sub
 
Upvote 0
One idea is to just run a sub when you want to set a format on a text value:

This sub would set all the cells to highlight yellow interior color if the text matches an inputted value. You could possibly input the color value (interior color index) you want also, etc. -- I suppose if you want you can set this to equal the value in C1 rather than have a value inputted.

Regards.

EDIT:
Code:
Sub MakeFormatPermanent()
Dim r As Range
Dim c As Range
Dim myText As String

msg = "Enter text." & vbCrLf & vbCrLf
msg = msg & "Cells with this text in the currently selected range will be formatted."

'get text
myText = InputBox(msg)
If myText = "" Then End

'Check cells - add format where text is found
For Each c In r

    If UCase(c.Value) = myText Then
        With c
        .FormatConditions.Delete
        .Interior.ColorIndex = 6
        End With
    End If

Next c

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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