Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Conditional format

  Asked By: Josefina    Date: Oct 15    Category: MS Office    Views: 1297
  

Conditional format has a limit of three formats. I checked around and
observed there are examples using VBA of situations where more than
three formats are used. However, these examples change the background
only that I have seen so far. Is it possible to do something like this:
(Made up, not correct Excel programming):

select case ErrCell
Case 0
BackColor = 7
ForeColor = 7 'both white, show nothing
Case 1
BackColor = 4
ForeColor = 11
Case 2
BackColor = 7
Forecolor = 9
Case Else
BackColor = 0
ForeColor = 0 'both black, undefined error
End


Something like that, is it possible to control both the background and
foreground (text)? My Excel (Excel 95) book only gives suggestions for
changing the background.

Share: 

 

11 Answers Found

 
Answer #1    Answered By: Vidos Fischer     Answered On: Oct 15

That should work.........................

 
Answer #2    Answered By: Hoor Khan     Answered On: Oct 15

I'm coming in the middle of the thread, so, what version of Excel are you
using? Excel 2003 and earlier had three levels of conditional  formatting,
Excel 2007 has far more.

To find out how to do conditional formatting in VBA, use Record Macro and
play with the conditional formatting. This will give you some basic VBA code
to demonstrate how to code Conditional formatting.

 
Answer #3    Answered By: Hugo Williams     Answered On: Oct 15

I am using Excel 2000. (I should get a later version if for no other
reason than its programming  ability features, but I can't convince
anyone of that.<g>)

I have been working on this the past few hours, and it looks like seven
formats is preferable, which is why I was trying to do it in VBA. Just
can't seem to do the font color correctly, just background  color so far.
I am wondering if Excel 2000 has font color control  in VBA?

 
Answer #4    Answered By: Amelia Schmidt     Answered On: Oct 15

instead of forecolor use .font.color You will have to refer to the range you
are applying it to.

e.g. Activesheet.Range("A7").font.color=7

 
Answer #5    Answered By: Kristen Chavez     Answered On: Oct 15

e.g. Activesheet.Range("A7").font.colorindex=7

 
Answer #6    Answered By: Jennie Harris     Answered On: Oct 15

Does Excel 2000 have the macro recorder? That is usually a good way to get
an understanding of how UI interactions can be translated into VBA.

 
Answer #7    Answered By: Melissa King     Answered On: Oct 15

Yes, it has the recorder............................

 
Answer #8    Answered By: Clayton Richardson     Answered On: Oct 15

It does, but, it doesn't generate "Select Cases." At least, not for me.
:( In another email I am sending an abbreviated version of the macro I
tried to write.

 
Answer #9    Answered By: Adelinda Fischer     Answered On: Oct 15

Sounds like you may need a 'WorkSheet change  Event' to trigger if the cell
change was in the range of interest.

 
Answer #10    Answered By: Tamara Nguyen     Answered On: Oct 15

The macro recorder will give a trail of the commands you issue, Select Cases
are beyond its' capabilities.



The macro recorder is used to find out what code is needed to replicate
specific UI actions. Once you have determined the VBA equivalent, you can
add the code snippet to the Select Cases structure which is well documented
in the VBA help. So the big advantage to the recorder is to get an idea of
what codes are needed to replicate a specific UI action. You should then
have some method names or keywords to focus your research. If you get
several keywords, Google them and see if an example pops up.

 
Answer #11    Answered By: Ujala Hashmi     Answered On: Oct 15

I use excel  2003 but this method should also work for Excel 2000 as I've tested
it on both versions. I had to make a similar macro that would search a column
of data for a specific value and then change  the old value to a new value as
well as apply conditional  formatting such as font style, font color color, style
and/or cell background  color, font name, font size and search criteria (to find
partial or exact cell values). Here's how I did it:

Set up a worksheet where you can enter this conditional formatting
criteria...lets call it "ColG" (assuming that the conditional formatting is set
up to search only in column G.

The data it would search for and apply the conditional formatting would be kept
in a separate worksheet...let's call that worksheet "Data".

Worksheet "ColG" will be used to set up your edit-replace (Conditional
Formatting) criteria...for example, sheet "ColG" apply conditional formatting to
column G of your "Data" worksheet...

Basically the column headings for worksheet "ColG" would be set up as follows:

Cell A1 = Replace Old text  ...Cells A2 and down this column would specify what
the macro criterion should search for...e.g. "ABC"
Cell B1 = With New Text ...Cells B2 and down this columnwould specify what the
criterion would be replaced as...e.g. "CDE"...so ABC would be replaced with
CDE...or you could keep it the same if you are just wanting to change the cell
format.
Cell C1 = select  Font Style ...Cells C2 and down this column would be set up
with In List Data Validation allowing the user to select Regular, Bold, Italic
or Bold Italic...)
Cell D1 = Specify Color Code ...Cells D2 and down this column would be set up
with In List Data Validation allowing the user to selct a number between 1 - 10.
These number represent the color code:



1 = Black


2 = White


3 = Red


4 = Green


5 = Blue


6 = Yellow


7 = Magenta


8 = Light Blue



Cell E1 = Specify Cell Color Code ...Cells E2 and down this column would be set
up
with In List Data Validation allowing the user to selct a number
between 1 - 10. These number represent the color code:

1 = Black



2 = White



3 = Red



4 = Green



5 = Blue



6 = Yellow



7 = Magenta



8 = Light Blue


Cell F1 = Select Font Name ...Cells F2 and down this column would be set up with
In List Data Validation allowing the user to select Arial, Times New Roman,
Comic Sans MS or Century Gothic


Cell G1 = Specify Font Size...Cells G2 and down this column would specify the
font size (e.g. 8, 10, 11, 12, etc.)
Cell H1 = Select Partial or Exact Match ...Cells H2 and down this column would
be set up with In List Data Validation allowing the user to select "Partial" or
"Exact" (e.g. if "Partial" is chosen, then a cell specifying "ABCDEF" would be
changed because it contains "ABC"...or if "Exact" is chose, then cells that
equal only "ABC" would be changed)

Then in a separate worksheet, you would put your actual data that you want this
conditional formatting to search and replace...(e.g. Worksheet "Data")


I assigned variables in the code to represent the values within worksheet "ColG"
(old text, new text, font style, font
color color, style and/or cell background color, font name, font size
and search criteria). I then programmed a loop to run through the the variably
assigned cell values
and replace the cells in a specified column (e.g. column G of worksheet "Data" )
until the next cell downward in worksheet "ColG"
would be
null...meaning that it's reached the end of the conditional formatting criteria)

The code would look like this:

Sub Col_G_Rep()
'
' Col_G_Replace Macro
' Macro searches in Worksheet "Data" for partial or exact cell contents and
replaces the old text with the new text and conditional cell formatting.

'DO NOT change to order of the variables below!


Dim origT As String 'old text
Dim newT As String 'new text
Dim sFont As String 'font style
Dim sIndex As Integer 'font color
Dim iIndex As Integer 'cell color
Dim iFont As String 'font name
Dim iSize As Integer 'font size
Dim iCont As String 'specifies partial or exact cell contents

Sheets("ColG").Select 'Selects the worksheet that contains the conditional
formatting criteria
Range("A2").Select 'A1 is your column Heading so you do not what to select that.

While ActiveCell.Value <> "" 'DO NOT change to order of the ActiveCell VBA code
below!

'old text
origT = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right

'new text
newT = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right

'font style
sFont = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right

'font color
sIndex = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right

'cell background color
iIndex = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right

'font name
iFont = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right

'font size
iSize = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right

'match exact or partial cell contents
iCont = ActiveCell.Value
ActiveCell.Offset(0, 1).Select 'activates 1 cell to the right


Sheets("Data").Select ' the worksheet where the data is stored and where
conditional formatting will be applied.
Range("G2").Select ' G1 contains your column heading so you don't what it to
select that.
Range(Selection, Selection.End(xlDown)).Select 'selects cells in Column G
from Range G2 and downward...note that cells cannot be null until the end of
your column data.
Application.ReplaceFormat.Clear

Application.ReplaceFormat.Interior.ColorIndex = iIndex

With Application.ReplaceFormat.Font
.Name = iFont
.FontStyle = sFont
.Size = iSize
.ColorIndex = sIndex
End With


If iCont = ("Partial") Then
Selection.Replace What:=origT, Replacement:= newT, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True

ElseIf iCont = ("Exact") Then
Selection.Replace What:=origT, Replacement:= newT, LookAt:= _
xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True
End If

Sheets("ColG").Select
ActiveCell.Offset(1, -8).Select 'this moves to the next row and activates
the cell in column A of worksheet"ColG". I will reassign the values of this next
row to the variables

Wend
Sheets("Paste Data Here").Select
Range("A2").Select
End Sub

 
Didn't find what you were looking for? Find more on Conditional format Or get search suggestion and latest updates.




Tagged: