KNOWUREXCEL

Find Latest Excel formulas and Excel tricks and Excel VBA Codes to simply your work.

Search This Blog

Home
Macros
Excel Formulas
Conditional Formatting
Excel Tricks

Showing posts with the label Conditional Formatting

View all

Posts

How to fill black cells with color using conditional formatting in excel | Conditional Formatting in Excel

on January 22, 2018

Conditional Formatting Excel Tricks

Get link
Facebook
X
Pinterest
Email
Other Apps

July 20182
January 20185
December 20179

Labels

Conditional Formatting
Excel Formulas
Excel Tricks
Excel VBA

VBA Code to display Message when click on button

VBA Code to display Message when click on button Step 1: Open Excel Sheet and go to the Insert tab. Step 2: Insert one of the by taking shape in Insert Shapes Box. Step 3: After inserting shape right click on the shape and go to assign macro. Step 4: New Module window openes and insert the VBA Code shown below and save.(before save you must save your sheet as Macro - Enabled sheet) Step 5: Change the code as per your requirement as shown below. Step 6: Now click on the shape and you can see message when you click. VBA Code: Sub RoundedRectangle1_Click() MsgBox "Hello I Am Rahul" End Sub Changes to be made at: MsgBox " "

formula to sum numbers in a column in excel | how to sum total of numbers in a column | Excel Formulas

formula to sum numbers in a column in excel | how to sum total of numbers in a column | Excel Formulas To find sum of total numbers in a column : Example : If in an excel sheet numbers starts from Column A and Row 1 (i.e. A1) and numbers ends in Column and Row6 (i.e. A6) which is shown in below Picture: formula to sum numbers in a column in excel | how to sum total of numbers in a column | Excel Formulas The Insert the formula =sum(A1:A6) in cell A7 which is shown empty in the picture and then press enter. You will get the result in cell A7. The other method to sum is, drag the cells from A1 to A7 and press Alt + = and you will get the result in A7 cell.

How to Create Shortcut Key to Paste Special in Excel | VBA Code to Create Shortcut Key to Paste Special in Excel

How to Create Shortcut Key to Paste Special in Excel | VBA Code to Create Shortcut Key to Paste Special in Excel VBA Code to Create Shortcut Key to Paste Special in Excel: Sub PasteVal() Selection.PasteSpecial Paste:=xlValues End Sub Open Excel Sheet Press Alt + F11 Key Go to the Insert Menu and Click on new Module in New Module copy and paste the above VBA code and save. Now Press Alt + F8 and new windows will open which is shown in below figure, in that windows you will ask to enter a key to create shortcut for Paste Special Enter a letter in the box. How to Create Shortcut Key to Paste Special in Excel | VBA Code to Create Shortcut Key to Paste Special in Excel How to Create Shortcut Key to Paste Special in Excel | VBA Code to Create Shortcut Key to Paste Special in Excel

How to fill black cells with color using conditional formatting in excel | Conditional Formatting in Excel

How to fill black cells with color using conditional formatting in excel | Conditional Formatting in Excel Step1: Select the range of cells in excel sheet and click on conditional formatting in excel and select new rule which is shown in figure. How to fill black cells with color using conditional formatting in excel | Conditional Formatting in Excel Step 2: A window will open when u click on new rule, in that new rule windows select "format only cells that contain". In below edit rule description, select " Blanks" How to fill black cells with color using conditional formatting in excel | Conditional Formatting in Excel Then click on format and a new windows will open Step 3: Go to fill in format window ...

VBA Code to delete hidden rows in Excel Sheet | Macro to delete hidden rows in Excel Sheet

VBA Code to delete hidden rows in Excel Sheet | Macro to delete hidden rows in Excel Sheet VBA Code: Open Excel sheet and Press Alt F11 Click on Insert Button and Insert new module Paste the below code and save. Before saving the code you must enable the sheet as Macro Enabled Sheet. Sub deletehidden() For lp = 256 To 1 Step -1 If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else Next For lp = 65536 To 1 Step -1 If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else Next End Sub here in the above code change the range of rows i.e. For Lp = 65536 according to your requirement.

VBA Code to Sum Numbers and Text in the same cell in excel

Step 1: Open Excel Sheet with values and numbers in a same cells. Step 2: Press Alt + F11 on keyboard and you can see Microsoft Visual basic for applications window. Step 3: Go to Insert Menu and select Module. Step 4: In empty module, copy and paste below VBA code and save. (before save you must save your sheet as Macro - Enabled sheet) Step 5: Go to the Data sheet, at last cell where you want calculate use the formula: =numb(select cells) and press enter. Function Numb(RNG As Range) As String Dim TotalItems As Integer Dim rCell As Range Dim TSum As Long Dim TStr As String TSum = 0 TVar = "" For Each rCell In RNG.Cells If rCell.Value <> "" Then If InStr(rCell.Value, " ") Then arrSplit = Split(rCell.Value, " ") ...

vlookup formula in Excel | vlookup function in excel | vlookup formula in excel with examples | vlookup syntax

vlookup formula in Excel | vlookup function in excel | vlookup formula in excel with examples | vlookup syntax Vlookup Formula in excel finds the data related to the input value. General formula for Vlookup is Vlookup(Lookupvalue, Table Array, Col index, [range lookup]) Example : To find the lookup value first save the reference table array in sheet. (shown in Picture 1). Next go the cell where you want to look up the values . Type the above formula in cell B1. In the formula A1 = cell where you want to lookup value AA1:AB4 = Table which is used as reference 2 = in the table which value to find. Here you are finding the second value 0 = finding the exact value. (if want to find approximately type 1 true in place of zero) Type the values in cell A1 and you will get the results in B1 which is shown in Picture 3. formula to sum numbers...

VBA Code to format rows and columns in filtered excel sheet | Macro to format rows and columns in filtered excel sheet

VBA Code to format rows and columns in filtered excel sheet | Macro to format rows and columns in filtered excel sheet Steps to Execute VBA Code in Excel Sheet Step 1: Open Filtered Data Excel Sheet. Step 2: Press Alt + F11 on keyboard and you can see Microsoft Visual basic for applications window. Step 3: Go to Insert Menu and select Module. Step 4: In empty module, copy and paste below VBA code and save. (before save you must save your sheet as Macro - Enabled sheet) Step 5: Go to the Filtered Data sheet, select rows and columns where you want to format cells and go to Developer Menu and click on macro. You ill see Find Value Macro Macro, click and run the macro. VBA Code | Macro Sub Unhide() Dim Col As Range For Each Col In Selection.EntireColumn If Col.Hidden = False Then Col.EntireColumn.AutoFit End If Next Col End Sub VBA Code to assign seq...

Countif formula in excel | How countif formula works in excel | Countif function

Countif formula in excel | How Countif formula works in excel | Countif function Countif formula in excel counts the criteria in the data sheet Example: In Excel Sheet, if Column A and Column B contains repeated data, if we want to count one single name in column we use countif formula which counts only given criteria. (show in Picture) Countif formula in excel | How countif formula works in excel | Countif function Here is the formula =countif(range,criteria) here in the formula range = select from which data sheet you want to find the count of particular name. in formula criteria = name which you want to find. In the above picture range = (A:B) columns criteria = (Month or numbers in the columns) Countif formula in excel | How countif formula works in excel | Countif function in the above picture the formula is...

VBA Code to create shortcut key for Paste Special in Excel

VBA Code to create shortcut key for Paste Special in Excel Sub PasteVal() Selection.PasteSpecial Paste:=xlValues End Sub How to Create Shortcut Key to Paste Special in Excel | VBA Code to Create Shortcut Key to Paste Special in Excel

Knowurexcel