Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i format cell to change colour as the number increases
I want to format a cell to change colours as the total numbers increases from
10 to 20 and 30-100 with red, yellow and blue respectively in Ms excel? Also if i input a name on a cell, Iwant the cell to format it to indicate if it has appeared within last six months by indicating red or yellow for less than six month or more than six month respectively? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i format cell to change colour as the number increases
Lanre,
Here is the first part...as below. for the second part where is the date you comparing today dates with ;relative to the name ? Eg: enter the name in A2 , and If the date in B2 is more than X months previous to today then the colour is red or yellow.? Can you please define where the date you wnat to compare todays is '--------------------VBA CODE--------Format colors of cell based on value--------------- 'I used Cell Ar as the example Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) With Range("A2") Select Case Val(.Value) Case Is < 10 ..Interior.Color = vbRed Case Is < 20 ..Interior.Color = vbYellow Case Is < 100 ..Interior.Color = vbBlue Case Else ..Interior.Color = vbWhite End Select End With End Sub Lanre Bex Ayoade wrote: I want to format a cell to change colours as the total numbers increases from 10 to 20 and 30-100 with red, yellow and blue respectively in Ms excel? Also if i input a name on a cell, Iwant the cell to format it to indicate if it has appeared within last six months by indicating red or yellow for less than six month or more than six month respectively? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i format cell to change colour as the number increases
Thank you Stevebriz! The name will appear in A2, The date will appear in A3
which if is less than six month A4 should show yellow colour with 'VALID' showed on A4 else it should be red with 'expired' shown on A4. The date will be compare to today date. "stevebriz" wrote: Lanre, Here is the first part...as below. for the second part where is the date you comparing today dates with ;relative to the name ? Eg: enter the name in A2 , and If the date in B2 is more than X months previous to today then the colour is red or yellow.? Can you please define where the date you wnat to compare todays is '--------------------VBA CODE--------Format colors of cell based on value--------------- 'I used Cell Ar as the example Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) With Range("A2") Select Case Val(.Value) Case Is < 10 ..Interior.Color = vbRed Case Is < 20 ..Interior.Color = vbYellow Case Is < 100 ..Interior.Color = vbBlue Case Else ..Interior.Color = vbWhite End Select End With End Sub Lanre Bex Ayoade wrote: I want to format a cell to change colours as the total numbers increases from 10 to 20 and 30-100 with red, yellow and blue respectively in Ms excel? Also if i input a name on a cell, Iwant the cell to format it to indicate if it has appeared within last six months by indicating red or yellow for less than six month or more than six month respectively? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i format cell to change colour as the number increases
Lanre Here is your new sub with what you want Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then With Range("A2") Select Case Val(.Value) Case Is < 10 ..Interior.Color = vbRed Case Is < 20 ..Interior.Color = vbYellow Case Is < 100 ..Interior.Color = vbBlue Case Else ..Interior.Color = xlNone End Select End With End If With Range("A3") If DateDiff("m", .Value, Date) < 6 Then Range("A4").Value = "VALID" Range("A4").Interior.Color = vbYellow Else Range("A4").Value = "Expired" Range("A4").Interior.Color = vbRed End If End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i format cell to change colour as the number increases
Thank you Stevebriz, but i have not got it clearly, how do i input the
program in excel? please explain it to me like: select the cell you want the data to appear. type $a$2....... Lanre "stevebriz" wrote: Lanre Here is your new sub with what you want Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then With Range("A2") Select Case Val(.Value) Case Is < 10 ..Interior.Color = vbRed Case Is < 20 ..Interior.Color = vbYellow Case Is < 100 ..Interior.Color = vbBlue Case Else ..Interior.Color = xlNone End Select End With End If With Range("A3") If DateDiff("m", .Value, Date) < 6 Then Range("A4").Value = "VALID" Range("A4").Interior.Color = vbYellow Else Range("A4").Value = "Expired" Range("A4").Interior.Color = vbRed End If End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i format cell to change colour as the number increases
Lanre, Is there more cells than jsut A2 -A4 you want this to "effect" to occur on? If so please tell me how which cells you want this to affect. This is code that you put in a through VBA( Press ALT F11) and this brings up the Vbeditor You then double click where it says sheet1 in the project explorer and paste this code in (This is assuming you are working on sheet 1) Whenever you change A2 the color will change The ":valid" will checked when any cell on the sheet is changed....or do you want this to be automatic when you open the sheet? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i format cell to change colour as the number increases
Please see below, this is all i want to do, I will only need to input the
current date (A3) and get either 'valid' with blue colour or Expired with red colour in A4 A1 Ade Shola Bex Niyi A2 3/4/06 4/1/06 23/2/06 26/2/06 A3 31/8/06 31/8/06 31/8/06 31/8/06 A4 'valid' 'Expired' 'Expired' 'Expired' "stevebriz" wrote: Lanre, Is there more cells than jsut A2 -A4 you want this to "effect" to occur on? If so please tell me how which cells you want this to affect. This is code that you put in a through VBA( Press ALT F11) and this brings up the Vbeditor You then double click where it says sheet1 in the project explorer and paste this code in (This is assuming you are working on sheet 1) Whenever you change A2 the color will change The ":valid" will checked when any cell on the sheet is changed....or do you want this to be automatic when you open the sheet? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i format cell to change colour as the number increases
I cna make it so it just puts today date if the 3 row if you like? and
thenit will automatically work out the values in the 4 th row (A4)? Lanre Bex Ayoade wrote: Please see below, this is all i want to do, I will only need to input the current date (A3) and get either 'valid' with blue colour or Expired with red colour in A4 A1 Ade Shola Bex Niyi A2 3/4/06 4/1/06 23/2/06 26/2/06 A3 31/8/06 31/8/06 31/8/06 31/8/06 A4 'valid' 'Expired' 'Expired' 'Expired' |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i format cell to change colour as the number increases
Thank you! I pasted below on it after double clicking the sheet 1 where i
have the data but nothing shows. Select Case Val(.Value) Case Is < 10 ...Interior.Color = vbRed Case Is < 20 ...Interior.Color = vbYellow Case Is < 100 ...Interior.Color = vbBlue Case Else ...Interior.Color = xlNone End Select End With End If With Range("A3") If DateDiff("m", .Value, Date) < 6 Then Range("A4").Value = "VALID" Range("A4").Interior.Color = vbYellow Else Range("A4").Value = "Expired" Range("A4").Interior.Color = vbRed End If End With End Sub "stevebriz" wrote: I cna make it so it just puts today date if the 3 row if you like? and thenit will automatically work out the values in the 4 th row (A4)? Lanre Bex Ayoade wrote: Please see below, this is all i want to do, I will only need to input the current date (A3) and get either 'valid' with blue colour or Expired with red colour in A4 A1 Ade Shola Bex Niyi A2 3/4/06 4/1/06 23/2/06 26/2/06 A3 31/8/06 31/8/06 31/8/06 31/8/06 A4 'valid' 'Expired' 'Expired' 'Expired' |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i format cell to change colour as the number increases
Did you save the workbook and reopen and click enable macros after
adding the code? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i format cell to change colour as the number increases
Try this one...if should do want you want as below
Private Sub CommandButton3_Click() Dim j As Integer On Error GoTo Errhandler For j = 1 To 4 ' set number columns wide you want If you have 4 names then this is 4 If Cells(2, j).Value < "" Then ' check the row 2 for the date is not empty If Len(Cells(2, j).Value) = 8 Then Cells(3, j).Value = Date ' enters today date in row 3 for each name If DateDiff("m", Cells(2, j).Value, Date) <= 6 Then ' checks date is less/equal than/to 6 months Cells(4, j).Value = "VALID" Cells(4, j).Interior.Color = vbBlue Else Cells(4, j).Value = "Expired" Cells(4, j).Interior.Color = vbRed End If Else MsgBox " Invalid Date Entry in Vell " & Replace(Cells(2, j).Address, "$", "") Cells(4, j).Value = "" Cells(2, j).Value = "" Cells(4, j).Interior.ColorIndex = xlNone End If Else Cells(4, j).Value = "" Cells(4, j).Interior.ColorIndex = xlNone End If Next j Exit Sub Errhandler: If Err.Number = 13 Then MsgBox " Invalid Date Entry in Vell " & Replace(Cells(2, j).Address, "$", "") ' for if he date is not entered right Cells(2, j).ClearContents Err.Clear Else End If End Sub Lanre Bex Ayoade wrote: Please see below, this is all i want to do, I will only need to input the current date (A3) and get either 'valid' with blue colour or Expired with red colour in A4 A1 Ade Shola Bex Niyi A2 3/4/06 4/1/06 23/2/06 26/2/06 A3 31/8/06 31/8/06 31/8/06 31/8/06 A4 'valid' 'Expired' 'Expired' 'Expired' "stevebriz" wrote: Lanre, Is there more cells than jsut A2 -A4 you want this to "effect" to occur on? If so please tell me how which cells you want this to affect. This is code that you put in a through VBA( Press ALT F11) and this brings up the Vbeditor You then double click where it says sheet1 in the project explorer and paste this code in (This is assuming you are working on sheet 1) Whenever you change A2 the color will change The ":valid" will checked when any cell on the sheet is changed....or do you want this to be automatic when you open the sheet? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditionl format a cell to change text colour | Excel Discussion (Misc queries) | |||
Change the colour of numbers in a cell if the number is negative | Excel Worksheet Functions | |||
change a cell background colour to my own RGB colour requirements | Excel Discussion (Misc queries) | |||
Change cell Colour when a number of days have been passed | Excel Discussion (Misc queries) | |||
Change colour of Text if number in cell exceed limit | Excel Worksheet Functions |