Re: Conditional Formatting the Dates
Hi Mangesh,
Thanks for the information.
BTW, where are you from?
Kris
Re: Conditional Formatting the Dates
Hi Mangesh,
Thanks for the information.
BTW, where are you from?
Kris
Re: Conditional Formatting the Dates
Hi Norie,
Your code is also working fine.
I am a beginner in VBA. So could you tell me the situation where Cases to be used and Ifs to be used.
Kris
Re: List Box data determining adjacent cell values
Hi Nathan,
You could use vlookup for this.
On Sheet2 in B1 try
=VLOOKUP(A2,Sheet1!A2:B100,2,0)
where A2 houses the EmpName
Sheet1A2 houses also the EmpName
Sheet1B2 houses the BasicSalary
Does this helps..
Kris
Re: Conditional Formatting the Dates
Hi Mangesh,
Well, I come up with the following code.
Private Sub Worksheet_Calculate()
Dim Dt As Variant
Dim Rng As Range
Dt = Date
Set Rng = Range("C7:C" & [C65536].End(xlUp).Row)
For Each Dt In Rng
If Dt < Date Then
Dt.Interior.ColorIndex = 3
ElseIf Dt = Date Then
Dt.Interior.ColorIndex = 38
ElseIf Dt = Date + 1 Then
Dt.Interior.ColorIndex = 36
ElseIf Dt = Date + 2 Then
Dt.Interior.ColorIndex = 35
ElseIf Dt > Date + 2 Then
Dt.Interior.ColorIndex = 50
Else
Dt.Interior.ColorIndex = 0
End If
Next Dt
End Sub
Display More
Kris
Re: Conditional Formatting the Dates
Hi Mangesh,
Thanks for the reply. Your code is for a particular cell. I would like to have an event code and my range will be
myRange = Range("C7:C" & Range("C65536").End(xlUp).Row)
Any idea?
Kris
Re: Extract data in one cell and place it in multiple cells
Hi Steve,
I am not sure about this, but did you try Data->Text to Columns
Kris
Hi All,
I would like to know how can I conditionally format dates in a cell by VBA.
for e.g. if the date in a cell:
<today():red
=today():lightred
today()+1:lightyellow
today()+2:lightgreen
>today()+2:teal(colorindex=14)
I have to use this code for a range which is a part of pivot table.
The range starts from C7
Any help would be appreciated.
Kris
Re: converting phone numbers
Hi,
Assumes your number is in A1
Try,
=SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(")",A1)+2),")","")," ","")
Kris
Re: Indian Currency Format
QuoteHere is the UDF by Parry
Sorry! It's not a UDF.
Kris
Re: Indian Currency Format
Hi,
Here is a contribution from Mr. Yogi Anand and a UDF from parry.
Custom cell format :
=REPLACE(TEXT(B2,"##"",""##"",""##"",""##"",""##"",""##"",""###"),1,6-INT((LEN(B2)>3)+(LEN(B2)-4)/2),"")
By Yogi.
Here is the UDF by Parry
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c
If Target.Cells.Count = 1 Then
Select Case Target.Value
Case Is >= 1000000000
Target.Cells.NumberFormat = "##"",""00"",""00"",""00"",""000.00"
Case Is >= 10000000
Target.Cells.NumberFormat = "##"",""00"",""00"",""000.00"
Case Is >= 100000
Target.Cells.NumberFormat = "##"",""00"",""000.00"
Case Else
Target.Cells.NumberFormat = "##,###.00"
End Select
Else
For Each c In Target
Select Case c.Value
Case Is >= 1000000000
c.NumberFormat = "##"",""00"",""00"",""00"",""000.00"
Case Is >= 10000000
c.NumberFormat = "##"",""00"",""00"",""000.00"
Case Is >= 100000
c.NumberFormat = "##"",""00"",""000.00"
Case Else
c.NumberFormat = "##,###.00"
End Select
Next c
End If
End Sub
Display More
Kris