Search : in
By :

Conditional Formatting (more than 3) in Excel

Last answer on Oct 31, 2009 5:29:40 pm GMT allanabanana, on Sep 1, 2008 12:15:26 am BST 
 Report this message to moderators

Hello,
i am trying to apply more than three options to excel that changes the background colour of the cell.

my range is R7:R1000

i need a VBA or macro that will change the background colour of the cell as follows

if cell reads "Extreme", change background to red

If Cell reads "High" change background to Purple

If Cell reads "Medium", change background to yellow

If cell reads "Low" change background to green

If cell is empty, do nothing

any assistance would be much appreciated

THANKS

Configuration: Windows XP
Internet Explorer 6.0

Best answers for « Conditional Formatting (more than 3) in Excel » in :
Unlimited Conditional Formatting ShowUnlimited Conditional Formatting Introduction Principle Code Introduction The limitation of MFC (Conditional Formatting) to Excel version 2007 is less than 3. This is very short. Having seen many applications to push the limit, I am...
Colouring cells on conditions ShowColouring cells on conditions There are many pratical functions under Excel which is not commonly used. Example: If you wish a cell automatically turns red (or other formatting border, frame etc) under one condition: a result, a...
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
Download AVStoDVD ShowIn most cases, programs offer to convert DVD to other formats more standardized and less bulky. But it is rare to find tools that are the opposite. AVStoDVD is a flexible tool for converting various video formats to DVD format. Advantage The video...
Download ABC Amber Text Converter Show
Download Speechi Light Show
Spreadsheets - Conditional expressions ShowWhat is a Conditional Structure? Conditional structures are instructions that allow to test if a condition is true or not. Conditional structures may be associated together. To successfully execute these tests using logical functions, spreadsheets...
MKV format (Matroska Video) ShowMKV format The MKV format (Matroska Video) is an entirely free video format. More precisely, it is a container (hence the name Matroska, in reference to the Russian dolls contained within another) making it possible to contain video (DivX, Xvid,...
Mini-DIN connector Show4-pin Mini-DIN connector The 4-pin Mini-DIN connector is used for transmitting analog video in S-Video format: More and more graphics cards these days have an S-Video socket built in, so that the computer's output can be viewed on a television...
Ask your question Reply

1

aquarelle, on Sep 1, 2008 3:54:18 am BST
  • +17

Hello,
Try with this macro, you have to write in Visual Basic Editor : 

Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("R7:R1000")
    For Each Cell In MyPlage
    
        If Cell.Value = "Extreme" Then
            Cell.Interior.ColorIndex = 3
        End If
        If Cell.Value = "Hight" Then
            Cell.Interior.ColorIndex = 4
        End If
        If Cell.Value = "Medium" Then
            Cell.Interior.ColorIndex = 18
        
        End If
        If Cell.Value = "Low" Then
            Cell.Interior.ColorIndex = 6
        End If
        
        If Cell.Value <> "Extreme" And Cell.Value <> "Hight" And Cell.Value <> "Medium" And Cell.Value <> "Low" Then
        Cell.Interior.ColorIndex = xlNone
        End If
        
    Next
End Sub


Hope this is what you want.

Best regards "Pour trouver une solution � ses probl�mes, il faut s'en donner la peine."

   
Reply to aquarelle

4

Roxythawer, on Dec 19, 2008 2:47:14 pm GMT
  • +10

I would like to do something similar but within an equation. So if certain condition is met that there is a numerical result and the result is displayed in a certain format of font colour or cell color. I would like to do this without going into the preset conditional formatting as I have more than 3 criterias.

   
Reply to Roxythawer

5

New2VBA, on Jan 14, 2009 3:15:47 pm GMT
  • +22

I have a similar problem, where I want an entire row to change color based ona value in that row. I need 6 colors, so Conditional formatting will not work. The macro above works great for the cell containing the variable to be used for the formatting, but how can I change the color of the entire row.

Thanks for the help.

   
Reply to New2VBA

7

OldYgg, on Mar 4, 2009 6:42:41 pm GMT
  • +2

I don't know if you've found the answer to this or not, but I ended up needing to figure this out for myself. Here is the code. Hopefully, this suits your needs as well.

I'm typing this in manually, so please excuse any typos. The code is on my other computer and I want to do this quickly.

(From the Visual Basic Editor)

Select Workbook - SheetChange

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh as Object, ByVal Target as Range)

Dim myRange As Range

Set myRange = Range("A1:K1000")

Dim iColor as Integer
Dim cell as Range

For Each cell in myRange

'checking the data in the first column only
If cell.Column = 1 Then

Select Case cell.Value
Case "701"
iColor = 6
Case "702"
iColor = 6
Case "101"
iColor = 4
Case "102"
iColor = 2
Case "301"
iColor = 47
Case "201"
iColor = 3
Case "202"
iColor = 4
Case Else
iColor = 2
End Select

'THIS COLORS IN THE CELL
'cell.Interior.ColorIndex = iColor

'This colors in the entire Row in Range
myRange.Rows(cell.row).Interior.ColorIndex = iColor

End If
Next

End Sub

   
Reply to OldYgg

10

TheMissingBite, on May 4, 2009 11:15:03 pm BST
  • +2

I created a spreadsheet (Test1) and tried to make changes to your code but I am very rusty with VB and I am struggling to recall how to make this work with my Excel spread sheet. My goal is to change the color of one cell (or row) based on the values 1 through 10 where each number corresponds to a different color. The value (1 through 10) is entered/calculated in the a cell in the same row (e.g. value 1 in A:1 changes the background color of cell B:1 to “some color”.

Can you please provide assistance/explain where to make changes to fit my Test1 spread sheet so I can understand the VB?

   
Reply to TheMissingBite

6

excel beefer, on Jan 15, 2009 5:58:43 pm GMT
  • +1

I love that code but I need to expend the formating to cellsnext to the data, IE if a1 =idle, I need to have the next 4 columns the same shade as the source (a1) any help?!

thanks

   
Reply to excel beefer

17

 OldYgg, on Aug 27, 2009 2:59:17 am BST
  • +1

To Expand the formatting just extend the cell range to the right.

   
Reply to OldYgg

9

TerryF, on Apr 21, 2009 1:17:05 pm BST
  • +1

The answer you gave to question regarding adding conditional formats is exactly what I am after - to change the cell colour from dark green through to red when inputting 0 through to 4. However, as I am unfamiliar with VB, I am unsure of how to input the formulae. I have tried several times but cannot get it to work. Can you please give me a step-by-step walkthough of what to do, what to open, how to save etc.

   
Reply to TerryF

2

blitz, on Dec 5, 2008 12:15:47 am GMT
  • +2

I would like to make it it like this..

if cell is empty the color of cell will change to yellow
anyone

   
Reply to blitz

3

Om bama Om, on Dec 5, 2008 6:37:20 pm GMT
  • +1

You have a lethargic as s, use the above code to suit your needs. you cant ask for more.

   
Reply to Om bama Om

8

Steve, on Mar 24, 2009 6:53:47 pm GMT
  • +4

You could try this..
in the cell you want to colour put the cond format based upon the fisrt cell that will result in the adjacent cells being coloured

=COUNTIF(AE10,"HD") + COUNTIF(AE10,"H") + COUNTIF(AE10,"T1")+ COUNTIF(AE10,"T2") + COUNTIF(AE10,"T3") + COUNTIF(AE10,"T4")+ COUNTIF(AE10,"T5")+ COUNTIF(AE10,"T6")+ COUNTIF(AE10,"T7")+ COUNTIF(AE10,"T7.5")

or the shorter version..
=SUMPRODUCT(--(LEFT(J18,1)="H")) + SUMPRODUCT(--(LEFT(J18,1)="T"))

just copy and paste the format onto the cells you want to cond format to be active for....

   
Reply to Steve

11

LostLogic, on May 6, 2009 6:37:52 am BST
  • +5

I encountered a similar "problem" if you will today. After hacking a bit this is what I ended up with.

' Run macro automaticly every time the Worksheet is changed
Private Sub Worksheet_Change(ByVal Target As Range)

' Define the range we wish to check against for x'es and v's.
Set ColorMagic = Range("C5:C24")

' Start checking each cell in the target range named ColorMagic for v's, x'es and nothings
For Each Cell in ColorMagic

If Cell.Value = "x" Then ' Color it red
Range("A" + CStr(Cell.Row) + ":C" + CStr(Cell.Row)).Interior.ColorIndex = 3
ElseIf Cell.Value = "v" Then ' Color it green
Range("A" + CStr(Cell.Row) + ":C" + CStr(Cell.Row)).Interior.ColorIndex = 4
else ' Remove all color
Range("A" + CStr(Cell.Row) + ":C" + CStr(Cell.Row)).Interior.ColorIndex = xlNone
End If

Next

End Sub

Now... This will change the cells in the range A to C on the checked row to Red if the value of the column C equals x, Green if it equals v and no color if it's anything else. This can be modified quite easily by adjusting the ranges and so fourth.
Hope this is of use to someone. :)
Cheers!

   
Reply to LostLogic

16

Midtown1000, on Aug 26, 2009 6:23:50 pm BST
  • +1

I tried your code for the additional colors. However, when I locked the cells and protected the sheet, it showed an error in the cell.internior.colorindex = xlNone line. I have several cells that are lists and every time I pick from a list, the error occurs but only when the sheet is protected.

Can you help?

   
Reply to Midtown1000

12

trying to learn VB, on May 21, 2009 6:49:18 pm BST

I understand how to use the macro to color many different colors within a spreadsheet, but if there are already some colors on the sheet, how would I tell the macro to leave the existing color if it has run through all of the tests and none of them apply. For example, at the end of the macro: it says
Case Else
icolor = 0
This will take out the color of any cell that doesn't meet any criteria. How would I tell it
Case Else
"just leave the cell alone and don't change the color"

Thank you for any advice.

   
Reply to trying to learn VB

13

vb_novice, on Aug 10, 2009 9:30:13 pm BST

I came across the code above from OldYgg and it works good for me except that I need to make it conditional. How can I put these two statements together

If cell.Column = 3 Then
Select Case cell.Value
Case Is <> "Total"

"and"

If cell.Column = 4 Then
Select Case cell.Value
Case "Total"
myRange.Rows(cell.Row).Select
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
End Select

'This colors in the entire Row in Range
'myRange.Rows(cell.Row).Interior.ColorIndex = iColor

End If
Next
End Sub

   
Reply to vb_novice

14

filip, on Aug 11, 2009 5:10:35 pm BST
  • +1

Hi,

try using GemBox Excel component for .NET. http://www.gemboxsoftware.com/GBSpreadsheet.htm
I've been using this component for some time and it's very useful and easy to work with. Here you can find lot of examples how to work with Excel file using this component. http://www.gemboxsoftware.com/LArticles/Excel-Automation-and­-Excel-Interop.htm

   
Reply to filip

15

TSIOUSTAS, on Aug 26, 2009 7:51:10 am BST

How may i buy advance contition formatihg

   
Reply to TSIOUSTAS
Ask your question Reply
They need your help