Search : in
By :

Conditional formatting using macros

Last answer on Sep 8, 2008 4:13:12 pm BST moses, on Jul 22, 2008 11:59:40 am BST 
 Report this message to moderators

I’ve got a worksheet and cell A1 to A100 consist of a drop down list which was created using data validation. When the user selects a value from the list, for that particular row, certain cells need to be filled with yellow. This is needed, because the worksheet consists a lot of fields to be filled in. to make it easy for the user to fill in the values, we need to highlight only the required cells based on the value the user selects from the drop down list.


Row 1 consists of fields
if "A" is selected from drop down list in cell A2, cell B2 and C2 needs to be filled with yellow
if "B" is selected from drop down list in cell A3, cell B3 & C3 & D3 needs to be filled with yellow

if nothing is selected, all the cells shouldnt be filled with color.

thanks,

Best answers for « Conditional formatting using macros » in :
Conditional formating - apply on more cells Show
Remove blank cell from conditional formatting Show
Avoid duplicates in Excel Show Avoid 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:...
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...
Copy rows based on a condition ShowCopy rows based on a condition Issue Solution Note Issue How can I have excel copy the entire row of data from worksheet Employee Inventory to another worksheet called EEs if column Q contains TERM. I've tried almost everything but...
Download HP USB Disk Storage Format Tool ShowHP USB Disk Storage Format Tool is a small utility allowing to format a key USB or READER MP4. It also allows to make a key (Bootable). This software works with all keys and readers MP4 of the market. To make work this utility under Windows...
Download HDD Low Level Format Tool Show
Download Format Factory ShowFormat Factory is an application that allows conversion of various types of media files. It has various characteristics that allow you to customize a lot of stuff like: Convert video formats, audio and picture shows. Repair damaged files. Reduce...
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,...
Formatting - Formatting a hard drive ShowBefore trying to understand formatting, you first need to understand how a hard drive works. Many people do not distinguish low-level formatting (also called physical formatting) from high-level formatting (also called logical formatting). Even...
Ask your question Reply

1

Ivan-hoe, on Jul 22, 2008 12:47:39 pm BST

Hello,
No need to use macros to do what you want to do. You can simply use Excel conditionnal formatting, with the formulas below :
for columns B and C : =OR($A2="A";$A2="B")
for column D : =$A2="B"

Nevertheless if you want to use a macro, you can use the event Worksheet_Change to have your macro run automatically 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range, MyColor
    If Intersect(Range("A1:A100"), Target) Is Nothing Then Exit Sub
    With Target
        .Range("B1:D1").Interior.Color = xlNone
        Select Case .Value
            Case Is = "A"
                Set MyRange = .Range("B1:C1")
                MyColor = 65535
            Case Is = "B"
                Set MyRange = .Range("B1:D1")
                MyColor = 65535
            Case Else
                Set MyRange = .Range("B1:D1")
                MyColor = xlNone
        End Select
        MyRange.Interior.Color = MyColor
    End With
End Sub

Ivan

   
Reply to Ivan-hoe

3

Sats, on Sep 7, 2008 7:46:57 pm BST

Hi Ivan,

I used this program to solve mz problem:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, MyColor
If Intersect(Range("A1:A100"), Target) Is Nothing Then Exit Sub
With Target
.Range("B1:D1").Interior.Color = xlNone
Select Case .Value
Case Is = "A"
Set MyRange = .Range("B1:C1")
MyColor = 65535
Case Is = "B"
Set MyRange = .Range("B1:D1")
MyColor = 65535
Case Else
Set MyRange = .Range("B1:D1")
MyColor = xlNone
End Select
MyRange.Interior.Color = MyColor
End With
End Sub

can you please modify this program , I want use some Text value in stead coloring the column.

For eg: i want

Case Is = "A"
Set MyRange = .Range("B1:C1")
MyColor = 65535 instead color i want MyColor=abc value in column ........like this

regards,
sats

   
Reply to Sats

2

moses, on Jul 22, 2008 1:36:45 pm BST

Sorry i didnt mention earlier, the drop down list contains more than 3 values which i believe conditional formatting will not take care of.

   
Reply to moses

4

Ivan-hoe, on Sep 8, 2008 7:55:42 am BST

Hello Sats,
to insert some text in a cell, use its property Value
e.g. : 

Case Is = "A" 
   .range("B1").Value = "abc" 
Case Is = "B"
   .range("B1").Value = "pqr"
   .range("C1").Value = "xyz"

Ivan

   
Reply to Ivan-hoe

5

 Sats, on Sep 8, 2008 4:13:12 pm BST

Hello Ivan,

Yes, i got my desired output.

Thanks a lot for all this help.

Best Regards,
Satyendra

   
Reply to Sats
Ask your question Reply
They need your help